package com.wpzf.ndhc.utils;

import cn.hutool.crypto.asymmetric.SM2;
import com.alibaba.nacos.shaded.com.google.gson.Gson;
import com.alibaba.nacos.shaded.com.google.gson.GsonBuilder;
import com.wpzf.ndhc.domain.*;
import com.wpzf.ndhc.mapper.WpSjsbNdDTOMapper;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.sqlite.mc.SQLiteMCSqlCipherConfig;

import java.io.File;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.security.Security;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;


public class DBUtil {

    public Object isNull(Object object) {
        if (object == null) {
            return null;
        } else if ("".equals(object + "")) {
            return "";
        } else {
            return object;
        }
    }


    String privateKey = "E4F22E79D141717AA63231A9DF53F4062545F17950BCB87B184B0128C6CC494E";
    String publicKey = "04D33B320ED4819E4BC0CBE579EA8C1EC5D23A63922C3A62C7DF46EE800777363B3F97B3E103D47933052F793BA1C55EAB5826B58A886D9FFE2565F3C8EFCCD8B1";

    public Map<String, Object> dbCreate(WpSjsbNdDTOMapper wpSjsbNdDTOMapper, String xzqdm) {
        Connection connection = null;
        Statement statement = null;
        Map<String, Object> rest = new HashMap<>();
        try {
            String dbPass = UUID.randomUUID().toString().replace("-", "").substring(0, 16);
            System.out.println("pass:" + dbPass);

            // 创建保存路径
            String savePath = System.getProperty("user.dir") + "\\" + "dbFile";
            // 保存文件的文件夹
            File folder = new File(savePath);
            // 判断路径是否存在,不存在则自动创建
            if (!folder.exists()) {
                folder.mkdirs();
            }

            String dbPath = savePath + "\\" + xzqdm + ".db";
            File file = new File(dbPath);
            if (file.exists()) {
                file.delete();
            }
            connection = SQLiteMCSqlCipherConfig.getV3Defaults().withKey(dbPass).build().createConnection("jdbc:sqlite:" + dbPath);

            statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            Map<String, Object> param = new HashMap<>();
            param.put("id", "1"); //抽取地块的查询条件
            param.put("xzqdm", xzqdm); //抽取地块的查询条件

            List<WpSjsbDkjbxxNdDTO> dkjbxxList = wpSjsbNdDTOMapper.getallDkjbxx(param);

            String createTableSQL = "";

            // 01 创建扩展信息元数据表
            createTableSQL = "CREATE TABLE KZXXYSJ(BSM INT,DKLX TEXT,ZDMC TEXT,ZDBM TEXT,ZDMS TEXT,ZDLX TEXT,ZDCD INT,SFBT TEXT,SJZD TEXT,KZXXLX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbKzxxysjNdDTO> lists = wpSjsbNdDTOMapper.getallKzxxysj(param);
            if (lists.size() > 0) {
                for (int i = 0; i < lists.size(); i++) {
                    WpSjsbKzxxysjNdDTO wpSjsbKzxxysjNdDTO = lists.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO KZXXYSJ (BSM,DKLX,ZDMC,ZDBM,ZDMS,ZDLX,ZDCD,SFBT,SJZD,KZXXLX) VALUES (?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setInt(1, wpSjsbKzxxysjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbKzxxysjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbKzxxysjNdDTO.getZdmc());
                        preparedStatement.setString(4, wpSjsbKzxxysjNdDTO.getZdbm());
                        preparedStatement.setString(5, wpSjsbKzxxysjNdDTO.getZdms());
                        preparedStatement.setString(6, wpSjsbKzxxysjNdDTO.getZdlx());
                        if (wpSjsbKzxxysjNdDTO.getZdcd() == null) {
                            preparedStatement.setNull(7, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(7, wpSjsbKzxxysjNdDTO.getZdcd());
                        }
                        preparedStatement.setString(8, wpSjsbKzxxysjNdDTO.getSfbt());
                        preparedStatement.setString(9, wpSjsbKzxxysjNdDTO.getSjzd());
                        preparedStatement.setString(10, wpSjsbKzxxysjNdDTO.getKzxxlx());
                        int rowsAffected = preparedStatement.executeUpdate();
                    }
                }
            }

            // 02 图斑基本信息 TBJBXX
            createTableSQL = "CREATE TABLE TBJBXX(BSM TEXT,DKLX TEXT,XZQDM TEXT,XMC TEXT,TBBH TEXT,SFBHZDK TEXT,TBMJ REAL,XZB REAL,YZB REAL,BZ TEXT,KZXX TEXT,DKFW TEXT, SJDRLY TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbjbxxNdDTO> listtbjbxxs = wpSjsbNdDTOMapper.getallTbjbxx(param);
            if (listtbjbxxs.size() > 0) {
                for (int i = 0; i < listtbjbxxs.size(); i++) {
                    WpSjsbTbjbxxNdDTO wpSjsbTbjbxxNdDTO = listtbjbxxs.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO TBJBXX (BSM,DKLX,XZQDM,XMC,TBBH,SFBHZDK,TBMJ,XZB,YZB,BZ,KZXX,DKFW,SJDRLY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbTbjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbjbxxNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbTbjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbTbjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbTbjbxxNdDTO.getTbbh());
                        preparedStatement.setString(6, wpSjsbTbjbxxNdDTO.getSfbhzdk());
                        preparedStatement.setDouble(7, wpSjsbTbjbxxNdDTO.getTbmj());
                        preparedStatement.setDouble(8, wpSjsbTbjbxxNdDTO.getXzb());
                        preparedStatement.setDouble(9, wpSjsbTbjbxxNdDTO.getYzb());
                        preparedStatement.setString(10, wpSjsbTbjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbTbjbxxNdDTO.getKzxx());
                        preparedStatement.setString(12, wpSjsbTbjbxxNdDTO.getDkfw());
                        preparedStatement.setString(13, wpSjsbTbjbxxNdDTO.getSjdrly());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //03 图斑调查核实信息表（TBDCHSXX）
            createTableSQL = "CREATE TABLE TBDCHSXX(BSM TEXT,TBBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT, KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbdchsxxNdDTO> listtbdchsxxs = wpSjsbNdDTOMapper.getallTbdchsxx(dkjbxxList);
            if (listtbdchsxxs.size() > 0) {
                for (int i = 0; i < listtbdchsxxs.size(); i++) {
                    WpSjsbTbdchsxxNdDTO wpSjsbTbdchsxxNdDTO = listtbdchsxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO TBDCHSXX (BSM,TBBSM,WYHSQK,DCRY,DCSJ, KZXX) VALUES (?,?,?,?,?,?)");
                    {
                        preparedStatement.setLong(1, wpSjsbTbdchsxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbdchsxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbTbdchsxxNdDTO.getWyhsqk());
                        preparedStatement.setString(4, wpSjsbTbdchsxxNdDTO.getDcry());
                        preparedStatement.setString(5, wpSjsbTbdchsxxNdDTO.getDcsj());
                        preparedStatement.setString(6, wpSjsbTbdchsxxNdDTO.getKzxx());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //04 地块基本信息表（DKJBXX）
            createTableSQL = "CREATE TABLE DKJBXX( BSM  TEXT,TBBSM  TEXT,XZQDM  TEXT,XMC  TEXT,DKBH  TEXT,DKMC  TEXT,DKMJ  REAL,XZB  REAL,YZB  REAL,BZ  TEXT,DKFW  TEXT,KZXX  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkjbxxNdDTO> listdkjbxxbs = wpSjsbNdDTOMapper.getallDkjbxx(param);
            Gson gson1 = new GsonBuilder().serializeNulls().create();
            if (listdkjbxxbs.size() > 0) {
                for (int i = 0; i < listdkjbxxbs.size(); i++) {
                    WpSjsbDkjbxxNdDTO wpSjsbDkjbxxNdDTO = listdkjbxxbs.get(i);//数据库实体
                    Map<String, Object> dkmap = new HashMap<>();
                    dkmap.put("SFBGJSYD", wpSjsbDkjbxxNdDTO.getSfbgjsyd());
                    dkmap.put("SFGDWF", wpSjsbDkjbxxNdDTO.getSfgdwf());
                    dkmap.put("GDMJ", wpSjsbDkjbxxNdDTO.getGdmj());
                    dkmap.put("QTNYDMJ", wpSjsbDkjbxxNdDTO.getQtnydmj());
                    dkmap.put("JSYDMJ", wpSjsbDkjbxxNdDTO.getJsydmj());
                    dkmap.put("WLYDMJ", wpSjsbDkjbxxNdDTO.getWlydmj());
                    dkmap.put("PDLX", wpSjsbDkjbxxNdDTO.getPdlx());
                    dkmap.put("HF_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx1()));
                    dkmap.put("HF_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx2()));
                    dkmap.put("HF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getHf_sjyt()));
                    dkmap.put("HF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getHf_pdyjsm()));
                    dkmap.put("WF_WFLX1", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx1()));
                    dkmap.put("WF_WFLX2", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx2()));
                    dkmap.put("WF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getWf_sjyt()));
                    dkmap.put("SFLZGDJF", isNull(wpSjsbDkjbxxNdDTO.getSflzgdjf()));
                    dkmap.put("SFWHZJ", isNull(wpSjsbDkjbxxNdDTO.getSfwhzj()));
                    dkmap.put("SFDPF", isNull(wpSjsbDkjbxxNdDTO.getSfdpf()));
                    dkmap.put("WF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getWf_pdyjsm()));
                    dkmap.put("XMMC", isNull(wpSjsbDkjbxxNdDTO.getXmmc()));
                    dkmap.put("XMZT", isNull(wpSjsbDkjbxxNdDTO.getXmzt()));
                    dkmap.put("XMLX", isNull(wpSjsbDkjbxxNdDTO.getXmlx()));
                    dkmap.put("LXPZJG", isNull(wpSjsbDkjbxxNdDTO.getLxpzjg()));
                    dkmap.put("LXPZWH", isNull(wpSjsbDkjbxxNdDTO.getLxpzwh()));
                    dkmap.put("WF_SFLA", isNull(wpSjsbDkjbxxNdDTO.getWf_sfla()));
                    dkmap.put("QT_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx1()));
                    dkmap.put("QT_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx2()));
                    dkmap.put("PWSJ", isNull(wpSjsbDkjbxxNdDTO.getPwsj()));
                    dkmap.put("QT_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getQt_pdyjsm()));
                    dkmap.put("SFNRHSQD", isNull(wpSjsbDkjbxxNdDTO.getSfnrhsqd()));
                    dkmap.put("XZJD", isNull(wpSjsbDkjbxxNdDTO.getXzjd()));
                    dkmap.put("CSMC", isNull(wpSjsbDkjbxxNdDTO.getCsmc()));
                    dkmap.put("XXDZ", isNull(wpSjsbDkjbxxNdDTO.getXxdz()));

                    String listToJsonString1 = gson1.toJson(dkmap);

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKJBXX (BSM,TBBSM,XZQDM,XMC,DKBH,DKMC,DKMJ,XZB,YZB,BZ,DKFW,KZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbDkjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkjbxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbDkjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbDkjbxxNdDTO.getDkbh());
                        preparedStatement.setString(6, wpSjsbDkjbxxNdDTO.getDkmc());
                        preparedStatement.setDouble(7, wpSjsbDkjbxxNdDTO.getDkmj());
                        preparedStatement.setDouble(8, wpSjsbDkjbxxNdDTO.getXzb());
                        preparedStatement.setDouble(9, wpSjsbDkjbxxNdDTO.getYzb());
                        preparedStatement.setString(10, wpSjsbDkjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbDkjbxxNdDTO.getDkfw());
                        preparedStatement.setString(12, listToJsonString1);
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //05 地块调查核实信息表（DKDCHSXX)
            createTableSQL = "CREATE TABLE DKDCHSXX(DKBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT,KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkdchssxNdDTO> listdkdchsxxs = wpSjsbNdDTOMapper.getallDkdchsxx(dkjbxxList);
            if (listdkdchsxxs.size() > 0) {
                for (int i = 0; i < listdkdchsxxs.size(); i++) {
                    WpSjsbDkdchssxNdDTO wpSjsbDkdchssxNdDTO = listdkdchsxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKDCHSXX (DKBSM,WYHSQK,DCRY,DCSJ,KZXX) VALUES (?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbDkdchssxNdDTO.getDkbsm());
                        preparedStatement.setString(2, wpSjsbDkdchssxNdDTO.getWyhsqk());
                        preparedStatement.setString(3, wpSjsbDkdchssxNdDTO.getDcry());
                        preparedStatement.setString(4, wpSjsbDkdchssxNdDTO.getDcsj());
                        preparedStatement.setString(5, wpSjsbDkdchssxNdDTO.getKzxx());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //06 地块附件信息表（DKFJJBXX）
            createTableSQL = "CREATE TABLE DKFJJBXX(BSM INTEGER,DKBSM TEXT,XZQDM TEXT,FJMC TEXT,FJLX   TEXT,SCRY  TEXT,SCSJ  TEXT,FJDX  INTEGER,MD5  TEXT,FJ BLOB,URL TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建地块附件信息表！");
            List<WpSjsbDkfjjbxxNdDTO> listdkfjjbxxs = wpSjsbNdDTOMapper.getallDkfjjbxx(param);
            if (listdkfjjbxxs.size() > 0) {
                for (int i = 0; i < listdkfjjbxxs.size(); i++) {
                    WpSjsbDkfjjbxxNdDTO wpSjsbDkfjjbxxNdDTO = listdkfjjbxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKFJJBXX (BSM,DKBSM,XZQDM,FJMC,FJLX,SCRY,SCSJ,FJDX,MD5,FJ,URL) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setLong(1, wpSjsbDkfjjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkfjjbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkfjjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkfjjbxxNdDTO.getFjmc());
                        preparedStatement.setString(5, wpSjsbDkfjjbxxNdDTO.getFjlx());
                        preparedStatement.setString(6, wpSjsbDkfjjbxxNdDTO.getScry());
                        preparedStatement.setString(7, wpSjsbDkfjjbxxNdDTO.getScsj());
                        if (wpSjsbDkfjjbxxNdDTO.getFjdx() == null) {
                            preparedStatement.setNull(8, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkfjjbxxNdDTO.getFjdx());
                        }
                        preparedStatement.setString(9, wpSjsbDkfjjbxxNdDTO.getMd5());
                        if (wpSjsbDkfjjbxxNdDTO.getFj() == null) {
                            preparedStatement.setNull(10, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(10, wpSjsbDkfjjbxxNdDTO.getFj());
                        }
                        preparedStatement.setString(11, wpSjsbDkfjjbxxNdDTO.getUrl());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //07 地块批文信息表（DKPWJBXX）
            createTableSQL = "CREATE TABLE DKPWJBXX(BSM  TEXT, DKBSM  TEXT,XZQDM  TEXT,PZWH  TEXT,PZWHLX  TEXT,PZWHLXTWO  TEXT,PZJG  TEXT,PWLY INTEGER,PZSJ  TEXT,PZMJ  REAL,PZGDMJ  REAL,PZNYDMJ  REAL,PZYJJBNTMJ  REAL,YXQZ  TEXT,PWLB  TEXT,XMMC  TEXT,PZJSYDMJ  REAL,PZWLYMJ  REAL,FJURL  TEXT,SHPURL  TEXT,MD5  TEXT,REASON  TEXT,FJMC  TEXT,FJSLMC  TEXT,FJBIN BLOB,FJSLBIN BLOB)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkpwjbbxxNdDTO> listdkpwjbxxs = wpSjsbNdDTOMapper.getallDkwpjbxx(param);
            if (listdkpwjbxxs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listdkpwjbxxs.size(); i++) {
                    WpSjsbDkpwjbbxxNdDTO wpSjsbDkpwjbbxxNdDTO = listdkpwjbxxs.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKPWJBXX (BSM,DKBSM,XZQDM,PZWH,PZWHLX,PZWHLXTWO,PZJG,PWLY,PZSJ,PZMJ,PZGDMJ,PZNYDMJ,PZYJJBNTMJ,YXQZ,PWLB,XMMC,PZJSYDMJ,PZWLYMJ,FJURL,SHPURL,MD5,REASON,FJMC,FJSLMC,FJBIN,FJSLBIN) " +
                                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbDkpwjbbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkpwjbbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkpwjbbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkpwjbbxxNdDTO.getPzwh());
                        preparedStatement.setString(5, wpSjsbDkpwjbbxxNdDTO.getPzwhlx());
                        preparedStatement.setString(6, wpSjsbDkpwjbbxxNdDTO.getPzwhlxtwo());
                        preparedStatement.setString(7, wpSjsbDkpwjbbxxNdDTO.getPzjg());
                        if (wpSjsbDkpwjbbxxNdDTO.getPwly() == null) {
                            preparedStatement.setInt(8, Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkpwjbbxxNdDTO.getPwly());
                        }
                        preparedStatement.setString(9, wpSjsbDkpwjbbxxNdDTO.getPzsj());
                        preparedStatement.setDouble(10, wpSjsbDkpwjbbxxNdDTO.getPzmj());
                        preparedStatement.setDouble(11, wpSjsbDkpwjbbxxNdDTO.getPzgdmj());
                        preparedStatement.setDouble(12, wpSjsbDkpwjbbxxNdDTO.getPznydmj());

                        preparedStatement.setDouble(13, wpSjsbDkpwjbbxxNdDTO.getPzyjjbntmj());
                        preparedStatement.setString(14, wpSjsbDkpwjbbxxNdDTO.getYxqz());
                        preparedStatement.setString(15, wpSjsbDkpwjbbxxNdDTO.getPwlb());
                        preparedStatement.setString(16, wpSjsbDkpwjbbxxNdDTO.getXmmc());
                        preparedStatement.setDouble(17, wpSjsbDkpwjbbxxNdDTO.getPzjsydmj());
                        preparedStatement.setDouble(18, wpSjsbDkpwjbbxxNdDTO.getPzwlymj());
                        preparedStatement.setString(19, wpSjsbDkpwjbbxxNdDTO.getFjurl());
                        preparedStatement.setString(20, wpSjsbDkpwjbbxxNdDTO.getShpurl());
                        preparedStatement.setString(21, wpSjsbDkpwjbbxxNdDTO.getMd5());
                        preparedStatement.setString(22, wpSjsbDkpwjbbxxNdDTO.getReason());
                        preparedStatement.setString(23, wpSjsbDkpwjbbxxNdDTO.getFjmc());
                        preparedStatement.setString(24, wpSjsbDkpwjbbxxNdDTO.getFjslmc());
                        if (wpSjsbDkpwjbbxxNdDTO.getFjbin() == null) {
                            preparedStatement.setNull(25, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(25, wpSjsbDkpwjbbxxNdDTO.getFjbin());
                        }
                        if (wpSjsbDkpwjbbxxNdDTO.getFjslbin() == null) {
                            preparedStatement.setNull(26, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(26, wpSjsbDkpwjbbxxNdDTO.getFjslbin());
                        }
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //08 附件表（FJ）
            createTableSQL = "CREATE TABLE FJ(BSM TEXT,DKLX TEXT,DKBSM TEXT,ZDKBH TEXT,XZQDM TEXT,FJMC TEXT,FJLX TEXT,PSTZ TEXT,FJ BLOB,FJHXZ TEXT,PSSJ TEXT,XDGD REAL,JDGD REAL,Longitude REAL,Latitude REAL,PSFYJ INT,PSJD INT,PSHGJ INT,PSJJ INT,FJYSKD INT,FJYSGD INT,PSRY TEXT,SPKZXX TEXT,ZSDM TEXT,JYM TEXT,URL TEXT,PSDXZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbFjNdDTO> listfjs = wpSjsbNdDTOMapper.getallFj(param);
            if (listfjs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listfjs.size(); i++) {
                    WpSjsbFjNdDTO wpSsjsbFjNdDTO = listfjs.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO FJ (BSM,DKLX,DKBSM,ZDKBH,XZQDM,FJMC,FJLX,PSTZ,FJ,FJHXZ,PSSJ,XDGD,JDGD,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSJJ,FJYSKD,FJYSGD,PSRY,SPKZXX,ZSDM,JYM,URL,PSDXZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSsjsbFjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSsjsbFjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSsjsbFjNdDTO.getDkbsm());
                        preparedStatement.setString(4, wpSsjsbFjNdDTO.getZdkbh());
                        preparedStatement.setString(5, wpSsjsbFjNdDTO.getXzqdm());
                        preparedStatement.setString(6, wpSsjsbFjNdDTO.getFjmc());
                        preparedStatement.setString(7, wpSsjsbFjNdDTO.getFjlx());
                        preparedStatement.setString(8, wpSsjsbFjNdDTO.getPstz());
                        if (wpSsjsbFjNdDTO.getFj() == null) {
                            preparedStatement.setNull(9, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(9, wpSsjsbFjNdDTO.getFj());
                        }
                        preparedStatement.setString(10, wpSsjsbFjNdDTO.getFjhxz());
                        preparedStatement.setString(11, wpSsjsbFjNdDTO.getPssj());


                        if (wpSsjsbFjNdDTO.getXdgd() == null) {
                            preparedStatement.setNull(12, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(12, wpSsjsbFjNdDTO.getXdgd());
                        }

                        if (wpSsjsbFjNdDTO.getJdgd() == null) {
                            preparedStatement.setNull(13, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(13, wpSsjsbFjNdDTO.getJdgd());
                        }

                        preparedStatement.setDouble(14, wpSsjsbFjNdDTO.getLongitude());
                        preparedStatement.setDouble(15, wpSsjsbFjNdDTO.getLatitude());
                        preparedStatement.setInt(16, wpSsjsbFjNdDTO.getPsfyj());
                        preparedStatement.setInt(17, wpSsjsbFjNdDTO.getPsjd());
                        preparedStatement.setInt(18, wpSsjsbFjNdDTO.getPshgj());
                        preparedStatement.setInt(19, wpSsjsbFjNdDTO.getPsjj());
                        preparedStatement.setInt(20, wpSsjsbFjNdDTO.getFjyskd());
                        preparedStatement.setInt(21, wpSsjsbFjNdDTO.getFjysgd());
                        preparedStatement.setString(22, wpSsjsbFjNdDTO.getPsry());
                        preparedStatement.setString(23, wpSsjsbFjNdDTO.getSpkzxx());
                        //证书代码
                        {
                            //wpSsjsbFjNdDTO.getZsdm()
                            preparedStatement.setString(24, "API32_YXK33YEGLXMUVMOYEQ43JWE4EU");
                        }
                        //校验码 用于校验数据的真伪
                        //A=FJHXZ,PSSJ,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSRY,ZSDM 采用英文逗号拼接后的字符串
                        String stra = concatValue(wpSsjsbFjNdDTO);
                        preparedStatement.setString(25, stra);
                        preparedStatement.setString(26, wpSsjsbFjNdDTO.getUrl());
                        preparedStatement.setString(27, wpSsjsbFjNdDTO.getPsdxzxx());

                        int rowsAffected = preparedStatement.executeUpdate();
                    }
                }
            }


            //09 地块审核信息表（DKSHXX）
            createTableSQL = "CREATE TABLE DKSHXX(BSM integer,DKBSM TEXT,DKLX TEXT,XZQDM TEXT,SHJD TEXT,SHRY TEXT,SHJG TEXT,SHSM TEXT,SHSJ TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkshxxNdDTO> listdkshxxs = wpSjsbNdDTOMapper.getallDkshxx(param);
            if (listdkshxxs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listdkshxxs.size(); i++) {
                    WpSjsbDkshxxNdDTO wpSjsbDkshxxNdDTO = listdkshxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKSHXX (BSM,DKBSM,DKLX,XZQDM,SHJD,SHRY,SHJG,SHSM,SHSJ) VALUES (?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setLong(1, wpSjsbDkshxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkshxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkshxxNdDTO.getDklx());
                        preparedStatement.setString(4, wpSjsbDkshxxNdDTO.getXzqdm());
                        preparedStatement.setString(5, wpSjsbDkshxxNdDTO.getShjd());

                        preparedStatement.setString(6, wpSjsbDkshxxNdDTO.getShry());
                        preparedStatement.setString(7, wpSjsbDkshxxNdDTO.getShjg());
                        preparedStatement.setString(8, wpSjsbDkshxxNdDTO.getShsm());
                        preparedStatement.setString(9, wpSjsbDkshxxNdDTO.getShsj());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }

            //10 加密信息表（JMXX）
            createTableSQL = "CREATE TABLE JMXX(ZSDM TEXT,SZZS TEXT,ZSBFJGDM TEXT,ZSBFJGGY TEXT)";
            statement.executeUpdate(createTableSQL);
            UUID uuid = UUID.randomUUID();
            String hexString = uuid.toString().replace("-", "").substring(0, 32);
            {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO JMXX(ZSDM,SZZS,ZSBFJGDM,ZSBFJGGY) VALUES (?,?,?,?)");
                {
                    preparedStatement.setString(1, hexString);
                    preparedStatement.setString(2, "eyJvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwib3JnbmFtZSI6IuS4reWbveWbveWcn+WLmOa1i+inhOWIkumZoijljavniYfmiafms5XmmbrmhafmoLjmn6UpIiwiY2VydGNvZGUiOiJBUEkzMl9ZWEszM1lFR0xYTVVWTU9ZRVE0M0pXRTRFVSIsImNlcnRvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwiY2VydG9yZ25hbWUiOiLkuK3lm73lm73lnJ/li5jmtYvop4TliJLpmaIiLCJleHBpcmF0aW9uIjoiMjAyNi0xMi0zMSAxNDozNDo0NSIsInB1YmxpY2tleSI6IjA0RDMzQjMyMEVENDgxOUU0QkMwQ0JFNTc5RUE4QzFFQzVEMjNBNjM5MjJDM0E2MkM3REY0NkVFODAwNzc3MzYzQjNGOTdCM0UxMDNENDc5MzMwNTJGNzkzQkExQzU1RUFCNTgyNkI1OEE4ODZEOUZGRTI1NjVGM0M4RUZDQ0Q4QjEiLCJzaWduIjoiODU5OTU4NDEyQ0U3QzFDOTA2QjQ0NjIwREEzNjdGNzIwNjA0RDVBOEY5NkNFNkI1QUYzQzA4MTIxMjlGRTM0QjIxQjJEODExNjYzRDY0OTIzRTYwMjVCQjI5MEEzNUY3NUExQjUwQzYzQ0RGNDQyQzUxNjJFM0NDQTQ0N0Y1MkMiLCJjZXJ0dHlwZSI6IuaOpeWPo+iwg+eUqOivgeS5piIsInJlZ2lvbmNvZGUiOiIwMDAwMDAifQ==");
                    preparedStatement.setString(3, "12100000400010398P");
                    preparedStatement.setString(4, "048B570397D9FFD9EFE79382E6E75D48DA16832556214293E4BB28BA0C59C1FC397176C83612158D025BF6B51CCE554C4D795DE0F1CB1469DC83499975AB245689");
                    int rowsAffected = preparedStatement.executeUpdate();
                    //System.out.println(rowsAffected + " 行被插入");
                }
            }
            //11 版本信息表（DB_INFO)
            createTableSQL = "CREATE TABLE DB_INFO(VERSION TEXT,CREATETIME TEXT,UPDATETIME TEXT,CREATEUSER TEXT,DESCRIPTION TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建版本信息表！");

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "INSERT INTO DB_INFO (VERSION,CREATETIME,UPDATETIME,CREATEUSER,DESCRIPTION) VALUES (?,?,?,?,?)");
            {
                preparedStatement.setString(1, "2.0");
                preparedStatement.setString(2, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((new Date()).getTime()));
                preparedStatement.setNull(3, Types.VARBINARY);
                preparedStatement.setString(4, "卫片执法");
                preparedStatement.setString(5, "WPZFNDTB2024待国家核查数据同步成果包");
                int rowsAffected = preparedStatement.executeUpdate();
                //System.out.println(rowsAffected + " 行被插入");
            }

            System.out.println("创建DB且插入数据成功！");
            rest.put("code", 200);
            rest.put("dbpath", dbPath);
            rest.put("dbpass", dbPass);
            rest.put("message", "创建DB成功");
            return rest;
        } catch (SQLException e) {
            System.out.println("连接数据库时发生错误：" + e.getMessage());
            rest.put("code", 500);
            rest.put("message", "连接数据库时发生错误：" + e.getMessage());
            return rest;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                System.out.println("关闭数据库连接时发生错误：" + e.getMessage());
            }
        }
    }

    private String concatValue(WpSjsbFjNdDTO wpSsjsbFjNdDTO) throws NoSuchAlgorithmException {
        Security.addProvider(new BouncyCastleProvider());
        //A=FJHXZ,PSSJ,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSRY,ZSDM 采用英文逗号拼接后的字符串。
        String strA = wpSsjsbFjNdDTO.getFjhxz() + "," + wpSsjsbFjNdDTO.getPssj() + "," + wpSsjsbFjNdDTO.getLongitude() + ","
                + wpSsjsbFjNdDTO.getLatitude() + "," + wpSsjsbFjNdDTO.getPsfyj() + "," + wpSsjsbFjNdDTO.getPsjd() + ","
                + wpSsjsbFjNdDTO.getPshgj() + "," + wpSsjsbFjNdDTO.getPsry() + ",API32_YXK33YEGLXMUVMOYEQ43JWE4EU";
        //B=SM3(A)，采用 SM3 进行哈希值计算（十六进制大写字符串）
        byte[] inputBytes = strA.getBytes();
        MessageDigest md = MessageDigest.getInstance("SM3");
        md.update(inputBytes, 0, inputBytes.length);
        byte[] hash = md.digest();
        StringBuilder sb = new StringBuilder();
        for (byte b : hash) {
            sb.append(String.format("%02x", b));
        }
        String value = sb.toString().toUpperCase();
        byte[] sourceData = value.getBytes();

        SM2 sm2 = new SM2(privateKey, publicKey);
        sm2.usePlainEncoding();
        byte[] signData = sm2.sign(sourceData);
        StringBuilder sb2 = new StringBuilder();
        for (byte b : signData) {
            sb2.append(String.format("%02x", b));
        }
        return sb2.toString().toUpperCase();
    }

    public Map<String, Object> dbCreate(WpSjsbNdDTOMapper wpSjsbNdDTOMapper, String xzqdm, String status) {
        Connection connection = null;
        Statement statement = null;
        Map<String, Object> rest = new HashMap<>();
        try {
            String dbPass = UUID.randomUUID().toString().replace("-", "").substring(0, 16);
            System.out.println("pass:" + dbPass);

            // 创建保存路径
            String savePath = System.getProperty("user.dir") + "\\" + "dbFile";
            // 保存文件的文件夹
            File folder = new File(savePath);
            // 判断路径是否存在,不存在则自动创建
            if (!folder.exists()) {
                folder.mkdirs();
            }

            String dbPath = savePath + "\\" + xzqdm + ".db";
            File file = new File(dbPath);
            if (file.exists()) {
                file.delete();
            }
            connection = SQLiteMCSqlCipherConfig.getV3Defaults().withKey(dbPass).build().createConnection("jdbc:sqlite:" + dbPath);

            connection.setAutoCommit(false); // Start a transaction

            statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            Map<String, Object> param = new HashMap<>();
            param.put("id", "1"); //抽取地块的查询条件
            param.put("xzqdm", xzqdm); //抽取地块的查询条件

            //根据 xzqdm 查出所有tb的bsm
            List<WpSjsbTbjbxxNdDTO> sjsbTbjbxxByStatusList = wpSjsbNdDTOMapper.getSjsbTbjbxxList(param);

            List<WpSjsbDkjbxxNdDTO> sjsbDkjbxxbByTbbsmList = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmList(sjsbTbjbxxByStatusList);

            String createTableSQL = "";

            // 01 创建扩展信息元数据表
            createTableSQL = "CREATE TABLE KZXXYSJ(BSM INT,DKLX TEXT,ZDMC TEXT,ZDBM TEXT,ZDMS TEXT,ZDLX TEXT,ZDCD INT,SFBT TEXT,SJZD TEXT,KZXXLX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbKzxxysjNdDTO> lists = wpSjsbNdDTOMapper.getallKzxxysj(param);
            if (lists != null && lists.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO KZXXYSJ (BSM,DKLX,ZDMC,ZDBM,ZDMS,ZDLX,ZDCD,SFBT,SJZD,KZXXLX) VALUES (?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < lists.size(); i++) {
                    WpSjsbKzxxysjNdDTO wpSjsbKzxxysjNdDTO = lists.get(i);//数据库实体

                    {
                        preparedStatement.setInt(1, wpSjsbKzxxysjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbKzxxysjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbKzxxysjNdDTO.getZdmc());
                        preparedStatement.setString(4, wpSjsbKzxxysjNdDTO.getZdbm());
                        preparedStatement.setString(5, wpSjsbKzxxysjNdDTO.getZdms());
                        preparedStatement.setString(6, wpSjsbKzxxysjNdDTO.getZdlx());
                        if (wpSjsbKzxxysjNdDTO.getZdcd() == null) {
                            preparedStatement.setNull(7, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(7, wpSjsbKzxxysjNdDTO.getZdcd());
                        }
                        preparedStatement.setString(8, wpSjsbKzxxysjNdDTO.getSfbt());
                        preparedStatement.setString(9, wpSjsbKzxxysjNdDTO.getSjzd());
                        preparedStatement.setString(10, wpSjsbKzxxysjNdDTO.getKzxxlx());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }

            // 02 图斑基本信息 TBJBXX
            createTableSQL = "CREATE TABLE TBJBXX(BSM TEXT,DKLX TEXT,XZQDM TEXT,XMC TEXT,TBBH TEXT,SFBHZDK TEXT,TBMJ REAL,XZB REAL,YZB REAL,BZ TEXT,KZXX TEXT,DKFW TEXT, SJDRLY TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbjbxxNdDTO> listtbjbxxs = wpSjsbNdDTOMapper.getSjsbTbjbxxByStatusList(param);
            if (listtbjbxxs != null && listtbjbxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO TBJBXX (BSM,DKLX,XZQDM,XMC,TBBH,SFBHZDK,TBMJ,XZB,YZB,BZ,KZXX,DKFW,SJDRLY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listtbjbxxs.size(); i++) {
                    WpSjsbTbjbxxNdDTO wpSjsbTbjbxxNdDTO = listtbjbxxs.get(i);//数据库实体

                    {
                        preparedStatement.setString(1, wpSjsbTbjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbjbxxNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbTbjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbTbjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbTbjbxxNdDTO.getTbbh());
                        preparedStatement.setString(6, wpSjsbTbjbxxNdDTO.getSfbhzdk());
                        preparedStatement.setDouble(7, wpSjsbTbjbxxNdDTO.getTbmj());
                        preparedStatement.setDouble(8, wpSjsbTbjbxxNdDTO.getXzb());
                        preparedStatement.setDouble(9, wpSjsbTbjbxxNdDTO.getYzb());
                        preparedStatement.setString(10, wpSjsbTbjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbTbjbxxNdDTO.getKzxx());
                        preparedStatement.setString(12, wpSjsbTbjbxxNdDTO.getDkfw());
                        preparedStatement.setString(13, wpSjsbTbjbxxNdDTO.getSjdrly());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }
            //03 图斑调查核实信息表（TBDCHSXX）
            createTableSQL = "CREATE TABLE TBDCHSXX(BSM TEXT,TBBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT, KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbdchsxxNdDTO> listtbdchsxxs = wpSjsbNdDTOMapper.getallTbdchsxx(sjsbDkjbxxbByTbbsmList);
            if (listtbdchsxxs != null && listtbdchsxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO TBDCHSXX (BSM,TBBSM,WYHSQK,DCRY,DCSJ, KZXX) VALUES (?,?,?,?,?,?)");
                for (int i = 0; i < listtbdchsxxs.size(); i++) {
                    WpSjsbTbdchsxxNdDTO wpSjsbTbdchsxxNdDTO = listtbdchsxxs.get(i);//数据库实体

                    {
                        preparedStatement.setLong(1, wpSjsbTbdchsxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbdchsxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbTbdchsxxNdDTO.getWyhsqk());
                        preparedStatement.setString(4, wpSjsbTbdchsxxNdDTO.getDcry());
                        preparedStatement.setString(5, wpSjsbTbdchsxxNdDTO.getDcsj());
                        preparedStatement.setString(6, wpSjsbTbdchsxxNdDTO.getKzxx());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }
            //04 地块基本信息表（DKJBXX）
            createTableSQL = "CREATE TABLE DKJBXX( BSM  TEXT,TBBSM  TEXT,XZQDM  TEXT,XMC  TEXT,DKBH  TEXT,DKMC  TEXT,DKMJ  REAL,XZB  REAL,YZB  REAL,BZ  TEXT,DKFW  TEXT,KZXX  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkjbxxNdDTO> listdkjbxxbs = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmList(sjsbTbjbxxByStatusList);
            Gson gson1 = new GsonBuilder().serializeNulls().create();
            if (listdkjbxxbs != null && listdkjbxxbs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKJBXX (BSM,TBBSM,XZQDM,XMC,DKBH,DKMC,DKMJ,XZB,YZB,BZ,DKFW,KZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listdkjbxxbs.size(); i++) {
                    WpSjsbDkjbxxNdDTO wpSjsbDkjbxxNdDTO = listdkjbxxbs.get(i);//数据库实体
                    Map<String, Object> dkmap = new HashMap<>();
                    dkmap.put("SFBGJSYD", wpSjsbDkjbxxNdDTO.getSfbgjsyd());
                    dkmap.put("SFGDWF", wpSjsbDkjbxxNdDTO.getSfgdwf());
                    dkmap.put("GDMJ", wpSjsbDkjbxxNdDTO.getGdmj());
                    dkmap.put("QTNYDMJ", wpSjsbDkjbxxNdDTO.getQtnydmj());
                    dkmap.put("JSYDMJ", wpSjsbDkjbxxNdDTO.getJsydmj());
                    dkmap.put("WLYDMJ", wpSjsbDkjbxxNdDTO.getWlydmj());
                    dkmap.put("PDLX", wpSjsbDkjbxxNdDTO.getPdlx());
                    dkmap.put("HF_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx1()));
                    dkmap.put("HF_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx2()));
                    dkmap.put("HF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getHf_sjyt()));
                    dkmap.put("HF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getHf_pdyjsm()));
                    dkmap.put("WF_WFLX1", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx1()));
                    dkmap.put("WF_WFLX2", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx2()));
                    dkmap.put("WF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getWf_sjyt()));
                    dkmap.put("SFLZGDJF", isNull(wpSjsbDkjbxxNdDTO.getSflzgdjf()));
                    dkmap.put("SFWHZJ", isNull(wpSjsbDkjbxxNdDTO.getSfwhzj()));
                    dkmap.put("SFDPF", isNull(wpSjsbDkjbxxNdDTO.getSfdpf()));
                    dkmap.put("WF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getWf_pdyjsm()));
                    dkmap.put("XMMC", isNull(wpSjsbDkjbxxNdDTO.getXmmc()));
                    dkmap.put("XMZT", isNull(wpSjsbDkjbxxNdDTO.getXmzt()));
                    dkmap.put("XMLX", isNull(wpSjsbDkjbxxNdDTO.getXmlx()));
                    dkmap.put("LXPZJG", isNull(wpSjsbDkjbxxNdDTO.getLxpzjg()));
                    dkmap.put("LXPZWH", isNull(wpSjsbDkjbxxNdDTO.getLxpzwh()));
                    dkmap.put("WF_SFLA", isNull(wpSjsbDkjbxxNdDTO.getWf_sfla()));
                    dkmap.put("QT_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx1()));
                    dkmap.put("QT_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx2()));
                    dkmap.put("PWSJ", isNull(wpSjsbDkjbxxNdDTO.getPwsj()));
                    dkmap.put("QT_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getQt_pdyjsm()));
                    dkmap.put("SFNRHSQD", isNull(wpSjsbDkjbxxNdDTO.getSfnrhsqd()));
                    dkmap.put("XZJD", isNull(wpSjsbDkjbxxNdDTO.getXzjd()));
                    dkmap.put("CSMC", isNull(wpSjsbDkjbxxNdDTO.getCsmc()));
                    dkmap.put("XXDZ", isNull(wpSjsbDkjbxxNdDTO.getXxdz()));
                    String listToJsonString1 = gson1.toJson(dkmap);

                    {
                        preparedStatement.setString(1, wpSjsbDkjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkjbxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbDkjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbDkjbxxNdDTO.getDkbh());
                        preparedStatement.setString(6, wpSjsbDkjbxxNdDTO.getDkmc());
                        if (wpSjsbDkjbxxNdDTO.getDkmj() == null) {
                            preparedStatement.setNull(7, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(7, wpSjsbDkjbxxNdDTO.getDkmj());
                        }
                        if (wpSjsbDkjbxxNdDTO.getXzb() == null) {
                            preparedStatement.setNull(8, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(8, wpSjsbDkjbxxNdDTO.getXzb());
                        }
                        if (wpSjsbDkjbxxNdDTO.getYzb() == null) {
                            preparedStatement.setNull(9, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(9, wpSjsbDkjbxxNdDTO.getYzb());
                        }
                        preparedStatement.setString(10, wpSjsbDkjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbDkjbxxNdDTO.getDkfw());
                        preparedStatement.setString(12, listToJsonString1);
                        preparedStatement.addBatch();
                        // int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }
            //05 地块调查核实信息表（DKDCHSXX)
            createTableSQL = "CREATE TABLE DKDCHSXX(DKBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT,KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkdchssxNdDTO> listdkdchsxxs = wpSjsbNdDTOMapper.getallDkdchsxx(sjsbDkjbxxbByTbbsmList);
            if (listdkdchsxxs != null && listdkdchsxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKDCHSXX (DKBSM,WYHSQK,DCRY,DCSJ,KZXX) VALUES (?,?,?,?,?)");
                for (int i = 0; i < listdkdchsxxs.size(); i++) {
                    WpSjsbDkdchssxNdDTO wpSjsbDkdchssxNdDTO = listdkdchsxxs.get(i);//数据库实体
                    {
                        preparedStatement.setString(1, wpSjsbDkdchssxNdDTO.getDkbsm());
                        preparedStatement.setString(2, wpSjsbDkdchssxNdDTO.getWyhsqk());
                        preparedStatement.setString(3, wpSjsbDkdchssxNdDTO.getDcry());
                        preparedStatement.setString(4, wpSjsbDkdchssxNdDTO.getDcsj());
                        preparedStatement.setString(5, wpSjsbDkdchssxNdDTO.getKzxx());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //06 地块附件信息表（DKFJJBXX）
            createTableSQL = "CREATE TABLE DKFJJBXX(BSM INTEGER,DKBSM TEXT,XZQDM TEXT,FJMC TEXT,FJLX   TEXT,SCRY  TEXT,SCSJ  TEXT,FJDX  INTEGER,MD5  TEXT,FJ BLOB,URL TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建地块附件信息表！");
            List<WpSjsbDkfjjbxxNdDTO> listdkfjjbxxs = wpSjsbNdDTOMapper.getSjsbDkfjjbxxList(sjsbDkjbxxbByTbbsmList);
            if (listdkdchsxxs != null && listdkfjjbxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKFJJBXX (BSM,DKBSM,XZQDM,FJMC,FJLX,SCRY,SCSJ,FJDX,MD5,FJ,URL) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listdkfjjbxxs.size(); i++) {
                    WpSjsbDkfjjbxxNdDTO wpSjsbDkfjjbxxNdDTO = listdkfjjbxxs.get(i);//数据库实体

                    {
                        preparedStatement.setLong(1, wpSjsbDkfjjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkfjjbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkfjjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkfjjbxxNdDTO.getFjmc());
                        preparedStatement.setString(5, wpSjsbDkfjjbxxNdDTO.getFjlx());
                        preparedStatement.setString(6, wpSjsbDkfjjbxxNdDTO.getScry());
                        preparedStatement.setString(7, wpSjsbDkfjjbxxNdDTO.getScsj());
                        if (wpSjsbDkfjjbxxNdDTO.getFjdx() == null) {
                            preparedStatement.setNull(8, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkfjjbxxNdDTO.getFjdx());
                        }
                        preparedStatement.setString(9, wpSjsbDkfjjbxxNdDTO.getMd5());
                        if (wpSjsbDkfjjbxxNdDTO.getFj() == null) {
                            preparedStatement.setNull(10, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(10, wpSjsbDkfjjbxxNdDTO.getFj());
                        }
                        preparedStatement.setString(11, wpSjsbDkfjjbxxNdDTO.getUrl());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //07 地块批文信息表（DKPWJBXX）
            createTableSQL = "CREATE TABLE DKPWJBXX(BSM  TEXT, DKBSM  TEXT,XZQDM  TEXT,PZWH  TEXT,PZWHLX  TEXT,PZWHLXTWO  TEXT,PZJG  TEXT,PWLY INTEGER,PZSJ  TEXT,PZMJ  REAL,PZGDMJ  REAL,PZNYDMJ  REAL,PZYJJBNTMJ  REAL,YXQZ  TEXT,PWLB  TEXT,XMMC  TEXT,PZJSYDMJ  REAL,PZWLYMJ  REAL,FJURL  TEXT,SHPURL  TEXT,MD5  TEXT,REASON  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkpwjbbxxNdDTO> listdkpwjbxxs = wpSjsbNdDTOMapper.getSjsbDkwpjbxxList(sjsbDkjbxxbByTbbsmList);
            if (listdkpwjbxxs != null && listdkpwjbxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKPWJBXX (BSM,DKBSM,XZQDM,PZWH,PZWHLX,PZWHLXTWO,PZJG,PWLY,PZSJ,PZMJ,PZGDMJ,PZNYDMJ,PZYJJBNTMJ,YXQZ,PWLB,XMMC,PZJSYDMJ,PZWLYMJ,FJURL,SHPURL,MD5,REASON) " +
                                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listdkpwjbxxs.size(); i++) {
                    WpSjsbDkpwjbbxxNdDTO wpSjsbDkpwjbbxxNdDTO = listdkpwjbxxs.get(i);//数据库实体

                    {
                        preparedStatement.setString(1, wpSjsbDkpwjbbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkpwjbbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkpwjbbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkpwjbbxxNdDTO.getPzwh());
                        preparedStatement.setString(5, wpSjsbDkpwjbbxxNdDTO.getPzwhlx());
                        preparedStatement.setString(6, wpSjsbDkpwjbbxxNdDTO.getPzwhlxtwo());
                        preparedStatement.setString(7, wpSjsbDkpwjbbxxNdDTO.getPzjg());
                        if (wpSjsbDkpwjbbxxNdDTO.getPwly() == null) {
                            preparedStatement.setInt(8, Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkpwjbbxxNdDTO.getPwly());
                        }
                        preparedStatement.setString(9, wpSjsbDkpwjbbxxNdDTO.getPzsj());
                        preparedStatement.setDouble(10, wpSjsbDkpwjbbxxNdDTO.getPzmj());
                        preparedStatement.setDouble(11, wpSjsbDkpwjbbxxNdDTO.getPzgdmj());
                        preparedStatement.setDouble(12, wpSjsbDkpwjbbxxNdDTO.getPznydmj());

                        preparedStatement.setDouble(13, wpSjsbDkpwjbbxxNdDTO.getPzyjjbntmj());
                        preparedStatement.setString(14, wpSjsbDkpwjbbxxNdDTO.getYxqz());
                        preparedStatement.setString(15, wpSjsbDkpwjbbxxNdDTO.getPwlb());
                        preparedStatement.setString(16, wpSjsbDkpwjbbxxNdDTO.getXmmc());
                        if (wpSjsbDkpwjbbxxNdDTO.getPzjsydmj() == null) {
                            preparedStatement.setNull(17, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(17, wpSjsbDkpwjbbxxNdDTO.getPzjsydmj());
                        }
                        if (wpSjsbDkpwjbbxxNdDTO.getPzwlymj() == null) {
                            preparedStatement.setNull(18, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(18, wpSjsbDkpwjbbxxNdDTO.getPzwlymj());
                        }
                        preparedStatement.setString(19, wpSjsbDkpwjbbxxNdDTO.getFjurl());
                        preparedStatement.setString(20, wpSjsbDkpwjbbxxNdDTO.getShpurl());
                        preparedStatement.setString(21, wpSjsbDkpwjbbxxNdDTO.getMd5());
                        preparedStatement.setString(22, wpSjsbDkpwjbbxxNdDTO.getReason());

                        //int rowsAffected = preparedStatement.executeUpdate();
                        preparedStatement.addBatch();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //08 附件表（FJ）
            createTableSQL = "CREATE TABLE FJ(BSM TEXT,DKLX TEXT,DKBSM TEXT,ZDKBH TEXT,XZQDM TEXT,FJMC TEXT,FJLX TEXT,PSTZ TEXT,FJ,FJHXZ TEXT,PSSJ TEXT,XDGD REAL,JDGD REAL,Longitude REAL,Latitude REAL,PSFYJ INT,PSJD INT,PSHGJ INT,PSJJ INT,FJYSKD INT,FJYSGD INT,PSRY TEXT,SPKZXX TEXT,ZSDM TEXT,JYM TEXT,URL TEXT,PSDXZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbFjNdDTO> listfjs = wpSjsbNdDTOMapper.getSjsbFjList(sjsbDkjbxxbByTbbsmList);
            if (listfjs != null && listfjs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO FJ (BSM,DKLX,DKBSM,ZDKBH,XZQDM,FJMC,FJLX,PSTZ,FJ,FJHXZ,PSSJ,XDGD,JDGD,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSJJ,FJYSKD,FJYSGD,PSRY,SPKZXX,ZSDM,JYM,URL,PSDXZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listfjs.size(); i++) {
                    WpSjsbFjNdDTO wpSsjsbFjNdDTO = listfjs.get(i);//数据库实体
                    {
                        preparedStatement.setString(1, wpSsjsbFjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSsjsbFjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSsjsbFjNdDTO.getDkbsm());
                        preparedStatement.setString(4, wpSsjsbFjNdDTO.getZdkbh());
                        preparedStatement.setString(5, wpSsjsbFjNdDTO.getXzqdm());
                        preparedStatement.setString(6, wpSsjsbFjNdDTO.getFjmc());
                        preparedStatement.setString(7, wpSsjsbFjNdDTO.getFjlx());
                        preparedStatement.setString(8, wpSsjsbFjNdDTO.getPstz());
                        if (wpSsjsbFjNdDTO.getFj() == null) {
                            preparedStatement.setNull(9, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(9, wpSsjsbFjNdDTO.getFj());
                        }
                        preparedStatement.setString(10, wpSsjsbFjNdDTO.getFjhxz());
                        preparedStatement.setString(11, wpSsjsbFjNdDTO.getPssj());


                        if (wpSsjsbFjNdDTO.getXdgd() == null) {
                            preparedStatement.setNull(12, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(12, wpSsjsbFjNdDTO.getXdgd());
                        }

                        if (wpSsjsbFjNdDTO.getJdgd() == null) {
                            preparedStatement.setNull(13, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(13, wpSsjsbFjNdDTO.getJdgd());
                        }

                        preparedStatement.setDouble(14, wpSsjsbFjNdDTO.getLongitude());
                        preparedStatement.setDouble(15, wpSsjsbFjNdDTO.getLatitude());
                        preparedStatement.setInt(16, wpSsjsbFjNdDTO.getPsfyj());
                        preparedStatement.setInt(17, wpSsjsbFjNdDTO.getPsjd());
                        preparedStatement.setInt(18, wpSsjsbFjNdDTO.getPshgj());
                        preparedStatement.setInt(19, wpSsjsbFjNdDTO.getPsjj());
                        preparedStatement.setInt(20, wpSsjsbFjNdDTO.getFjyskd());
                        preparedStatement.setInt(21, wpSsjsbFjNdDTO.getFjysgd());
                        preparedStatement.setString(22, wpSsjsbFjNdDTO.getPsry());
                        preparedStatement.setString(23, wpSsjsbFjNdDTO.getSpkzxx());
                        //证书代码
                        {
                            //wpSsjsbFjNdDTO.getZsdm()
                            preparedStatement.setString(24, "API32_YXK33YEGLXMUVMOYEQ43JWE4EU");
                        }
                        //校验码 用于校验数据的真伪
                        //A=FJHXZ,PSSJ,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSRY,ZSDM 采用英文逗号拼接后的字符串
                        String stra = concatValue(wpSsjsbFjNdDTO);
                        preparedStatement.setString(25, stra);
                        preparedStatement.setString(26, wpSsjsbFjNdDTO.getUrl());
                        preparedStatement.setString(27, wpSsjsbFjNdDTO.getPsdxzxx());

                        //int rowsAffected = preparedStatement.executeUpdate();
                        preparedStatement.addBatch();
                    }
                }
                preparedStatement.executeBatch();

            }
            //09 地块审核信息表（DKSHXX）
            createTableSQL = "CREATE TABLE DKSHXX(BSM integer,DKBSM TEXT,DKLX TEXT,XZQDM TEXT,SHJD TEXT,SHRY TEXT,SHJG TEXT,SHSM TEXT,SHSJ TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkshxxNdDTO> listdkshxxs = wpSjsbNdDTOMapper.getSjsbDkshxxList(sjsbDkjbxxbByTbbsmList);
            if (listdkshxxs != null && listdkshxxs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKSHXX (BSM,DKBSM,DKLX,XZQDM,SHJD,SHRY,SHJG,SHSM,SHSJ) VALUES (?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listdkshxxs.size(); i++) {
                    WpSjsbDkshxxNdDTO wpSjsbDkshxxNdDTO = listdkshxxs.get(i);//数据库实体

                    {
                        preparedStatement.setLong(1, wpSjsbDkshxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkshxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkshxxNdDTO.getDklx());
                        preparedStatement.setString(4, wpSjsbDkshxxNdDTO.getXzqdm());
                        preparedStatement.setString(5, wpSjsbDkshxxNdDTO.getShjd());

                        preparedStatement.setString(6, wpSjsbDkshxxNdDTO.getShry());
                        preparedStatement.setString(7, wpSjsbDkshxxNdDTO.getShjg());
                        preparedStatement.setString(8, wpSjsbDkshxxNdDTO.getShsm());
                        preparedStatement.setString(9, wpSjsbDkshxxNdDTO.getShsj());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //10 加密信息表（JMXX）
            createTableSQL = "CREATE TABLE JMXX(ZSDM TEXT,SZZS TEXT,ZSBFJGDM TEXT,ZSBFJGGY TEXT)";
            statement.executeUpdate(createTableSQL);
            UUID uuid = UUID.randomUUID();
            String hexString = uuid.toString().replace("-", "").substring(0, 32);
            {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO JMXX(ZSDM,SZZS,ZSBFJGDM,ZSBFJGGY) VALUES (?,?,?,?)");
                {
                    preparedStatement.setString(1, hexString);
                    preparedStatement.setString(2, "eyJvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwib3JnbmFtZSI6IuS4reWbveWbveWcn+WLmOa1i+inhOWIkumZoijljavniYfmiafms5XmmbrmhafmoLjmn6UpIiwiY2VydGNvZGUiOiJBUEkzMl9ZWEszM1lFR0xYTVVWTU9ZRVE0M0pXRTRFVSIsImNlcnRvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwiY2VydG9yZ25hbWUiOiLkuK3lm73lm73lnJ/li5jmtYvop4TliJLpmaIiLCJleHBpcmF0aW9uIjoiMjAyNi0xMi0zMSAxNDozNDo0NSIsInB1YmxpY2tleSI6IjA0RDMzQjMyMEVENDgxOUU0QkMwQ0JFNTc5RUE4QzFFQzVEMjNBNjM5MjJDM0E2MkM3REY0NkVFODAwNzc3MzYzQjNGOTdCM0UxMDNENDc5MzMwNTJGNzkzQkExQzU1RUFCNTgyNkI1OEE4ODZEOUZGRTI1NjVGM0M4RUZDQ0Q4QjEiLCJzaWduIjoiODU5OTU4NDEyQ0U3QzFDOTA2QjQ0NjIwREEzNjdGNzIwNjA0RDVBOEY5NkNFNkI1QUYzQzA4MTIxMjlGRTM0QjIxQjJEODExNjYzRDY0OTIzRTYwMjVCQjI5MEEzNUY3NUExQjUwQzYzQ0RGNDQyQzUxNjJFM0NDQTQ0N0Y1MkMiLCJjZXJ0dHlwZSI6IuaOpeWPo+iwg+eUqOivgeS5piIsInJlZ2lvbmNvZGUiOiIwMDAwMDAifQ==");
                    preparedStatement.setString(3, "12100000400010398P");
                    preparedStatement.setString(4, "048B570397D9FFD9EFE79382E6E75D48DA16832556214293E4BB28BA0C59C1FC397176C83612158D025BF6B51CCE554C4D795DE0F1CB1469DC83499975AB245689");
                    int rowsAffected = preparedStatement.executeUpdate();
                    //System.out.println(rowsAffected + " 行被插入");
                }
            }
            //11 版本信息表（DB_INFO)
            createTableSQL = "CREATE TABLE DB_INFO(VERSION TEXT,CREATETIME TEXT,UPDATETIME TEXT,CREATEUSER TEXT,DESCRIPTION TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建版本信息表！");

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "INSERT INTO DB_INFO (VERSION,CREATETIME,UPDATETIME,CREATEUSER,DESCRIPTION) VALUES (?,?,?,?,?)");
            {
                preparedStatement.setString(1, "2.0");
                preparedStatement.setString(2, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((new Date()).getTime()));
                preparedStatement.setNull(3, Types.VARBINARY);
                preparedStatement.setString(4, "卫片执法");
                preparedStatement.setString(5, "WPZFNDTB2024待国家核查数据同步成果包");
                int rowsAffected = preparedStatement.executeUpdate();
                //System.out.println(rowsAffected + " 行被插入");
            }
            connection.commit(); // Commit the transaction

            System.out.println("创建DB且插入数据成功！");
            rest.put("code", 200);
            rest.put("dbpath", dbPath);
            rest.put("dbpass", dbPass);
            rest.put("message", "创建DB成功");
            return rest;
        } catch (SQLException e) {
            System.out.println("连接数据库时发生错误：" + e.getMessage());
            rest.put("code", 500);
            rest.put("message", "连接数据库时发生错误：" + e.getMessage());
            return rest;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                System.out.println("关闭数据库连接时发生错误：" + e.getMessage());
            }
        }
    }

    public Map<String, Object> getDBCreate(WpSjsbNdDTOMapper wpSjsbNdDTOMapper, String xzqdm, String status) {
        Connection connection = null;
        Statement statement = null;
        Map<String, Object> rest = new HashMap<>();
        try {
            String dbPass = UUID.randomUUID().toString().replace("-", "").substring(0, 16);
            System.out.println("pass:" + dbPass);

            // 创建保存路径
            String savePath = System.getProperty("user.dir") + "\\" + "getDBFile";
            // 保存文件的文件夹
            File folder = new File(savePath);
            // 判断路径是否存在,不存在则自动创建
            if (!folder.exists()) {
                folder.mkdirs();
            }

            String dbPath = savePath + "\\" + xzqdm + ".db";
            File file = new File(dbPath);
            if (file.exists()) {
                file.delete();
            }
            connection = SQLiteMCSqlCipherConfig.getV3Defaults().withKey(dbPass).build().createConnection("jdbc:sqlite:" + dbPath);

            connection.setAutoCommit(false); // Start a transaction

            statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            Map<String, Object> param = new HashMap<>();
            param.put("id", "1"); //抽取地块的查询条件
            if (xzqdm.length() >= 6) {
                // 截取前面6位
                param.put("xzqdm", xzqdm.substring(0, 6));
            }
            else
            {
                param.put("xzqdm", xzqdm);
            }
            param.put("stage", xzqdm);

            //根据 xzqdm 查出所有tb的bsm
            List<WpSjsbTbjbxxNdDTO> sjsbTbjbxxByStatusList = wpSjsbNdDTOMapper.getSjsbTbjbxxList(param);

            List<WpSjsbDkjbxxNdDTO> sjsbDkjbxxbByTbbsmList=null;
            if(sjsbTbjbxxByStatusList!=null && sjsbTbjbxxByStatusList.size()>0)
            {
                sjsbDkjbxxbByTbbsmList = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmList(sjsbTbjbxxByStatusList);
            }

            String createTableSQL = "";

            // 01 创建扩展信息元数据表
            createTableSQL = "CREATE TABLE KZXXYSJ(BSM INT,DKLX TEXT,ZDMC TEXT,ZDBM TEXT,ZDMS TEXT,ZDLX TEXT,ZDCD INT,SFBT TEXT,SJZD TEXT,KZXXLX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbKzxxysjNdDTO> lists = wpSjsbNdDTOMapper.getallKzxxysj(param);
            if (lists != null && lists.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO KZXXYSJ (BSM,DKLX,ZDMC,ZDBM,ZDMS,ZDLX,ZDCD,SFBT,SJZD,KZXXLX) VALUES (?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < lists.size(); i++) {
                    WpSjsbKzxxysjNdDTO wpSjsbKzxxysjNdDTO = lists.get(i);//数据库实体

                    {
                        preparedStatement.setInt(1, wpSjsbKzxxysjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbKzxxysjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbKzxxysjNdDTO.getZdmc());
                        preparedStatement.setString(4, wpSjsbKzxxysjNdDTO.getZdbm());
                        preparedStatement.setString(5, wpSjsbKzxxysjNdDTO.getZdms());
                        preparedStatement.setString(6, wpSjsbKzxxysjNdDTO.getZdlx());
                        if (wpSjsbKzxxysjNdDTO.getZdcd() == null) {
                            preparedStatement.setNull(7, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(7, wpSjsbKzxxysjNdDTO.getZdcd());
                        }
                        preparedStatement.setString(8, wpSjsbKzxxysjNdDTO.getSfbt());
                        preparedStatement.setString(9, wpSjsbKzxxysjNdDTO.getSjzd());
                        preparedStatement.setString(10, wpSjsbKzxxysjNdDTO.getKzxxlx());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }

            // 02 图斑基本信息 TBJBXX
            createTableSQL = "CREATE TABLE TBJBXX(BSM TEXT,DKLX TEXT,XZQDM TEXT,XMC TEXT,TBBH TEXT,SFBHZDK TEXT,TBMJ REAL,XZB REAL,YZB REAL,BZ TEXT,KZXX TEXT,DKFW TEXT, SJDRLY TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbjbxxNdDTO> listtbjbxxs = sjsbTbjbxxByStatusList;
            if (listtbjbxxs != null && listtbjbxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO TBJBXX (BSM,DKLX,XZQDM,XMC,TBBH,SFBHZDK,TBMJ,XZB,YZB,BZ,KZXX,DKFW,SJDRLY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listtbjbxxs.size(); i++) {
                    WpSjsbTbjbxxNdDTO wpSjsbTbjbxxNdDTO = listtbjbxxs.get(i);//数据库实体

                    {
                        preparedStatement.setString(1, wpSjsbTbjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbjbxxNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbTbjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbTbjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbTbjbxxNdDTO.getTbbh());
                        preparedStatement.setString(6, wpSjsbTbjbxxNdDTO.getSfbhzdk());
                        preparedStatement.setDouble(7, wpSjsbTbjbxxNdDTO.getTbmj());
                        preparedStatement.setDouble(8, wpSjsbTbjbxxNdDTO.getXzb());
                        preparedStatement.setDouble(9, wpSjsbTbjbxxNdDTO.getYzb());
                        preparedStatement.setString(10, wpSjsbTbjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbTbjbxxNdDTO.getKzxx());
                        preparedStatement.setString(12, wpSjsbTbjbxxNdDTO.getDkfw());
                        preparedStatement.setString(13, wpSjsbTbjbxxNdDTO.getSjdrly());
                        preparedStatement.addBatch();

                        //后续工具更新，该字段为必填值
 //                       Map<String, Object> param2 = new HashMap<>();
//                        param2.put("xzqdm",wpSjsbTbjbxxNdDTO.getXzqdm());
//                        param2.put("jcbh",wpSjsbTbjbxxNdDTO.getTbbh());
//                        Map<String, Object>  map2=wpSjsbNdDTOMapper.getXfInfo(param2);
//                        if(map2!=null && map2.size()>0) {
//                            Map<String, Object> tbmap = new HashMap<>();
//                            //dlrdgdmj,qtnydmj,dlrdjsydmj,dlrdwlydmj
//                            tbmap.put("GDMJ", map2.get("dlrdgdmj"));
//                            tbmap.put("QTNYDMJ", map2.get("qtnydmj"));
//                            tbmap.put("JSYDMJ", map2.get("dlrdjsydmj"));
//                            tbmap.put("WLYDMJ", map2.get("dlrdwlydmj"));
//                            String listToJsonString1 = gson2.toJson(tbmap);
//                            preparedStatement.setString(11, listToJsonString1);
//                        }

                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }
            //03 图斑调查核实信息表（TBDCHSXX）
            createTableSQL = "CREATE TABLE TBDCHSXX(BSM TEXT,TBBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT, KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbdchsxxNdDTO> listtbdchsxxs=null;
            if(sjsbDkjbxxbByTbbsmList!=null && sjsbDkjbxxbByTbbsmList.size()>0)
            {
                listtbdchsxxs = wpSjsbNdDTOMapper.getallTbdchsxx(sjsbDkjbxxbByTbbsmList);
            }
            if (listtbdchsxxs != null && listtbdchsxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO TBDCHSXX (BSM,TBBSM,WYHSQK,DCRY,DCSJ, KZXX) VALUES (?,?,?,?,?,?)");
                for (int i = 0; i < listtbdchsxxs.size(); i++) {
                    WpSjsbTbdchsxxNdDTO wpSjsbTbdchsxxNdDTO = listtbdchsxxs.get(i);//数据库实体


                    {
                        preparedStatement.setLong(1, wpSjsbTbdchsxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbdchsxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbTbdchsxxNdDTO.getWyhsqk());
                        preparedStatement.setString(4, wpSjsbTbdchsxxNdDTO.getDcry());
                        preparedStatement.setString(5, wpSjsbTbdchsxxNdDTO.getDcsj());
                        preparedStatement.setString(6, wpSjsbTbdchsxxNdDTO.getKzxx());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }
            //04 地块基本信息表（DKJBXX）
            createTableSQL = "CREATE TABLE DKJBXX( BSM  TEXT,TBBSM  TEXT,XZQDM  TEXT,XMC  TEXT,DKBH  TEXT,DKMC  TEXT,DKMJ  REAL,XZB  REAL,YZB  REAL,BZ  TEXT,DKFW  TEXT,KZXX  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkjbxxNdDTO> listdkjbxxbs = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmList(sjsbTbjbxxByStatusList);
            Gson gson1 = new GsonBuilder().serializeNulls().create();
            if (listdkjbxxbs != null && listdkjbxxbs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKJBXX (BSM,TBBSM,XZQDM,XMC,DKBH,DKMC,DKMJ,XZB,YZB,BZ,DKFW,KZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listdkjbxxbs.size(); i++) {
                    WpSjsbDkjbxxNdDTO wpSjsbDkjbxxNdDTO = listdkjbxxbs.get(i);//数据库实体
                    Map<String, Object> dkmap = new HashMap<>();
                    dkmap.put("SFBGJSYD", wpSjsbDkjbxxNdDTO.getSfbgjsyd());
                    dkmap.put("SFGDWF", wpSjsbDkjbxxNdDTO.getSfgdwf());
                    dkmap.put("GDMJ", wpSjsbDkjbxxNdDTO.getGdmj());
                    dkmap.put("QTNYDMJ", wpSjsbDkjbxxNdDTO.getQtnydmj());
                    dkmap.put("JSYDMJ", wpSjsbDkjbxxNdDTO.getJsydmj());
                    dkmap.put("WLYDMJ", wpSjsbDkjbxxNdDTO.getWlydmj());
                    dkmap.put("PDLX", wpSjsbDkjbxxNdDTO.getPdlx());
                    dkmap.put("HF_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx1()));
                    dkmap.put("HF_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx2()));
                    dkmap.put("HF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getHf_sjyt()));
                    dkmap.put("HF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getHf_pdyjsm()));
                    dkmap.put("WF_WFLX1", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx1()));
                    dkmap.put("WF_WFLX2", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx2()));
                    dkmap.put("WF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getWf_sjyt()));
                    dkmap.put("SFLZGDJF", isNull(wpSjsbDkjbxxNdDTO.getSflzgdjf()));
                    dkmap.put("SFWHZJ", isNull(wpSjsbDkjbxxNdDTO.getSfwhzj()));
                    dkmap.put("SFDPF", isNull(wpSjsbDkjbxxNdDTO.getSfdpf()));
                    dkmap.put("WF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getWf_pdyjsm()));
                    dkmap.put("XMMC", isNull(wpSjsbDkjbxxNdDTO.getXmmc()));
                    dkmap.put("XMZT", isNull(wpSjsbDkjbxxNdDTO.getXmzt()));
                    dkmap.put("XMLX", isNull(wpSjsbDkjbxxNdDTO.getXmlx()));
                    dkmap.put("LXPZJG", isNull(wpSjsbDkjbxxNdDTO.getLxpzjg()));
                    dkmap.put("LXPZWH", isNull(wpSjsbDkjbxxNdDTO.getLxpzwh()));
                    dkmap.put("WF_SFLA", isNull(wpSjsbDkjbxxNdDTO.getWf_sfla()));
                    dkmap.put("QT_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx1()));
                    dkmap.put("QT_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx2()));
                    dkmap.put("PWSJ", isNull(wpSjsbDkjbxxNdDTO.getPwsj()));
                    dkmap.put("QT_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getQt_pdyjsm()));
                    dkmap.put("SFNRHSQD", isNull(wpSjsbDkjbxxNdDTO.getSfnrhsqd()));
                    dkmap.put("XZJD", isNull(wpSjsbDkjbxxNdDTO.getXzjd()));
                    dkmap.put("CSMC", isNull(wpSjsbDkjbxxNdDTO.getCsmc()));
                    dkmap.put("XXDZ", isNull(wpSjsbDkjbxxNdDTO.getXxdz()));
                    String listToJsonString1 = gson1.toJson(dkmap);


                    {
                        preparedStatement.setString(1, wpSjsbDkjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkjbxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbDkjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbDkjbxxNdDTO.getDkbh());
                        preparedStatement.setString(6, wpSjsbDkjbxxNdDTO.getDkmc());
                        if (wpSjsbDkjbxxNdDTO.getDkmj() == null) {
                            preparedStatement.setNull(7, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(7, wpSjsbDkjbxxNdDTO.getDkmj());
                        }
                        if (wpSjsbDkjbxxNdDTO.getXzb() == null) {
                            preparedStatement.setNull(8, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(8, wpSjsbDkjbxxNdDTO.getXzb());
                        }
                        if (wpSjsbDkjbxxNdDTO.getYzb() == null) {
                            preparedStatement.setNull(9, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(9, wpSjsbDkjbxxNdDTO.getYzb());
                        }
                        preparedStatement.setString(10, wpSjsbDkjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbDkjbxxNdDTO.getDkfw());
                        preparedStatement.setString(12, listToJsonString1);
                        preparedStatement.addBatch();
                        // int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch(); // Execute all batched statements
            }
            //05 地块调查核实信息表（DKDCHSXX)
            createTableSQL = "CREATE TABLE DKDCHSXX(DKBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT,KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkdchssxNdDTO> listdkdchsxxs=null;
            if(sjsbDkjbxxbByTbbsmList!=null&&sjsbDkjbxxbByTbbsmList.size()>0)
            {
                 listdkdchsxxs = wpSjsbNdDTOMapper.getallDkdchsxx(sjsbDkjbxxbByTbbsmList);
            }
            if (listdkdchsxxs != null && listdkdchsxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKDCHSXX (DKBSM,WYHSQK,DCRY,DCSJ,KZXX) VALUES (?,?,?,?,?)");
                for (int i = 0; i < listdkdchsxxs.size(); i++) {
                    WpSjsbDkdchssxNdDTO wpSjsbDkdchssxNdDTO = listdkdchsxxs.get(i);//数据库实体
                    {
                        preparedStatement.setString(1, wpSjsbDkdchssxNdDTO.getDkbsm());
                        preparedStatement.setString(2, wpSjsbDkdchssxNdDTO.getWyhsqk());
                        preparedStatement.setString(3, wpSjsbDkdchssxNdDTO.getDcry());
                        preparedStatement.setString(4, wpSjsbDkdchssxNdDTO.getDcsj());
                        preparedStatement.setString(5, wpSjsbDkdchssxNdDTO.getKzxx());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //06 地块附件信息表（DKFJJBXX）
            createTableSQL = "CREATE TABLE DKFJJBXX(BSM INTEGER,DKBSM TEXT,XZQDM TEXT,FJMC TEXT,FJLX   TEXT,SCRY  TEXT,SCSJ  TEXT,FJDX  INTEGER,MD5  TEXT,FJ BLOB,URL TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建地块附件信息表！");
            List<WpSjsbDkfjjbxxNdDTO> listdkfjjbxxs=null;
            if(sjsbDkjbxxbByTbbsmList!=null &&sjsbDkjbxxbByTbbsmList.size()>0 )
            {
               listdkfjjbxxs = wpSjsbNdDTOMapper.getSjsbDkfjjbxxList(sjsbDkjbxxbByTbbsmList);
            }
            if (listdkdchsxxs != null && listdkfjjbxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKFJJBXX (BSM,DKBSM,XZQDM,FJMC,FJLX,SCRY,SCSJ,FJDX,MD5,FJ,URL) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listdkfjjbxxs.size(); i++) {
                    WpSjsbDkfjjbxxNdDTO wpSjsbDkfjjbxxNdDTO = listdkfjjbxxs.get(i);//数据库实体

                    {
                        preparedStatement.setLong(1, wpSjsbDkfjjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkfjjbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkfjjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkfjjbxxNdDTO.getFjmc());
                        preparedStatement.setString(5, wpSjsbDkfjjbxxNdDTO.getFjlx());
                        preparedStatement.setString(6, wpSjsbDkfjjbxxNdDTO.getScry());
                        preparedStatement.setString(7, wpSjsbDkfjjbxxNdDTO.getScsj());
                        if (wpSjsbDkfjjbxxNdDTO.getFjdx() == null) {
                            preparedStatement.setNull(8, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkfjjbxxNdDTO.getFjdx());
                        }
                        preparedStatement.setString(9, wpSjsbDkfjjbxxNdDTO.getMd5());
                        if (wpSjsbDkfjjbxxNdDTO.getFj() == null) {
                            preparedStatement.setNull(10, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(10, wpSjsbDkfjjbxxNdDTO.getFj());
                        }
                        preparedStatement.setString(11, wpSjsbDkfjjbxxNdDTO.getUrl());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //07 地块批文信息表（DKPWJBXX）
            createTableSQL = "CREATE TABLE DKPWJBXX(BSM  TEXT, DKBSM  TEXT,XZQDM  TEXT,PZWH  TEXT,PZWHLX  TEXT,PZWHLXTWO  TEXT,PZJG  TEXT,PWLY INTEGER,PZSJ  TEXT,PZMJ  REAL,PZGDMJ  REAL,PZNYDMJ  REAL,PZYJJBNTMJ  REAL,YXQZ  TEXT,PWLB  TEXT,XMMC  TEXT,PZJSYDMJ  REAL,PZWLYMJ  REAL,FJURL  TEXT,SHPURL  TEXT,MD5  TEXT,REASON  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkpwjbbxxNdDTO> listdkpwjbxxs=null;
            if(sjsbDkjbxxbByTbbsmList!=null &&sjsbDkjbxxbByTbbsmList.size()>0 )
            {
                listdkpwjbxxs = wpSjsbNdDTOMapper.getSjsbDkwpjbxxList(sjsbDkjbxxbByTbbsmList);
            }
            if (listdkpwjbxxs != null && listdkpwjbxxs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKPWJBXX (BSM,DKBSM,XZQDM,PZWH,PZWHLX,PZWHLXTWO,PZJG,PWLY,PZSJ,PZMJ,PZGDMJ,PZNYDMJ,PZYJJBNTMJ,YXQZ,PWLB,XMMC,PZJSYDMJ,PZWLYMJ,FJURL,SHPURL,MD5,REASON) " +
                                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listdkpwjbxxs.size(); i++) {
                    WpSjsbDkpwjbbxxNdDTO wpSjsbDkpwjbbxxNdDTO = listdkpwjbxxs.get(i);//数据库实体

                    {
                        preparedStatement.setString(1, wpSjsbDkpwjbbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkpwjbbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkpwjbbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkpwjbbxxNdDTO.getPzwh());
                        preparedStatement.setString(5, wpSjsbDkpwjbbxxNdDTO.getPzwhlx());
                        preparedStatement.setString(6, wpSjsbDkpwjbbxxNdDTO.getPzwhlxtwo());
                        preparedStatement.setString(7, wpSjsbDkpwjbbxxNdDTO.getPzjg());
                        if (wpSjsbDkpwjbbxxNdDTO.getPwly() == null) {
                            preparedStatement.setInt(8, Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkpwjbbxxNdDTO.getPwly());
                        }
                        preparedStatement.setString(9, wpSjsbDkpwjbbxxNdDTO.getPzsj());
                        preparedStatement.setDouble(10, wpSjsbDkpwjbbxxNdDTO.getPzmj());
                        preparedStatement.setDouble(11, wpSjsbDkpwjbbxxNdDTO.getPzgdmj());
                        preparedStatement.setDouble(12, wpSjsbDkpwjbbxxNdDTO.getPznydmj());

                        preparedStatement.setDouble(13, wpSjsbDkpwjbbxxNdDTO.getPzyjjbntmj());
                        preparedStatement.setString(14, wpSjsbDkpwjbbxxNdDTO.getYxqz());
                        preparedStatement.setString(15, wpSjsbDkpwjbbxxNdDTO.getPwlb());
                        preparedStatement.setString(16, wpSjsbDkpwjbbxxNdDTO.getXmmc());
                        if (wpSjsbDkpwjbbxxNdDTO.getPzjsydmj() == null) {
                            preparedStatement.setNull(17, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(17, wpSjsbDkpwjbbxxNdDTO.getPzjsydmj());
                        }
                        if (wpSjsbDkpwjbbxxNdDTO.getPzwlymj() == null) {
                            preparedStatement.setNull(18, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(18, wpSjsbDkpwjbbxxNdDTO.getPzwlymj());
                        }
                        preparedStatement.setString(19, wpSjsbDkpwjbbxxNdDTO.getFjurl());
                        preparedStatement.setString(20, wpSjsbDkpwjbbxxNdDTO.getShpurl());
                        preparedStatement.setString(21, wpSjsbDkpwjbbxxNdDTO.getMd5());
                        preparedStatement.setString(22, wpSjsbDkpwjbbxxNdDTO.getReason());

                        //int rowsAffected = preparedStatement.executeUpdate();
                        preparedStatement.addBatch();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //08 附件表（FJ）
            //只打包 违法附件
            List<WpSjsbDkjbxxNdDTO> sjsbDkjbxxbByTbbsmListwf = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmListwf(sjsbTbjbxxByStatusList);

            createTableSQL = "CREATE TABLE FJ(BSM TEXT,DKLX TEXT,DKBSM TEXT,ZDKBH TEXT,XZQDM TEXT,FJMC TEXT,FJLX TEXT,PSTZ TEXT,FJ,FJHXZ TEXT,PSSJ TEXT,XDGD REAL,JDGD REAL,Longitude REAL,Latitude REAL,PSFYJ INT,PSJD INT,PSHGJ INT,PSJJ INT,FJYSKD INT,FJYSGD INT,PSRY TEXT,SPKZXX TEXT,ZSDM TEXT,JYM TEXT,URL TEXT,PSDXZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbFjNdDTO> listfjs=null;
            if(sjsbDkjbxxbByTbbsmListwf!=null &&sjsbDkjbxxbByTbbsmListwf.size()>0 )
            {
                listfjs = wpSjsbNdDTOMapper.getSjsbFjList(sjsbDkjbxxbByTbbsmListwf);
            }
            //List<WpSjsbFjNdDTO> listfjs = wpSjsbNdDTOMapper.getSjsbFjList(sjsbDkjbxxbByTbbsmList);
            if (listfjs != null && listfjs.size() > 0) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO FJ (BSM,DKLX,DKBSM,ZDKBH,XZQDM,FJMC,FJLX,PSTZ,FJ,FJHXZ,PSSJ,XDGD,JDGD,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSJJ,FJYSKD,FJYSGD,PSRY,SPKZXX,ZSDM,JYM,URL,PSDXZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listfjs.size(); i++) {
                    WpSjsbFjNdDTO wpSsjsbFjNdDTO = listfjs.get(i);//数据库实体
                    {
                        preparedStatement.setString(1, wpSsjsbFjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSsjsbFjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSsjsbFjNdDTO.getDkbsm());
                        preparedStatement.setString(4, wpSsjsbFjNdDTO.getZdkbh());
                        preparedStatement.setString(5, wpSsjsbFjNdDTO.getXzqdm());
                        preparedStatement.setString(6, wpSsjsbFjNdDTO.getFjmc());
                        preparedStatement.setString(7, wpSsjsbFjNdDTO.getFjlx());
                        preparedStatement.setString(8, wpSsjsbFjNdDTO.getPstz());
                        if (wpSsjsbFjNdDTO.getFj() == null) {
                            preparedStatement.setNull(9, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(9, wpSsjsbFjNdDTO.getFj());
                        }
                        preparedStatement.setString(10, wpSsjsbFjNdDTO.getFjhxz());
                        preparedStatement.setString(11, wpSsjsbFjNdDTO.getPssj());


                        if (wpSsjsbFjNdDTO.getXdgd() == null) {
                            preparedStatement.setNull(12, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(12, wpSsjsbFjNdDTO.getXdgd());
                        }

                        if (wpSsjsbFjNdDTO.getJdgd() == null) {
                            preparedStatement.setNull(13, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(13, wpSsjsbFjNdDTO.getJdgd());
                        }

                        preparedStatement.setDouble(14, wpSsjsbFjNdDTO.getLongitude());
                        preparedStatement.setDouble(15, wpSsjsbFjNdDTO.getLatitude());
                        preparedStatement.setInt(16, wpSsjsbFjNdDTO.getPsfyj());
                        preparedStatement.setInt(17, wpSsjsbFjNdDTO.getPsjd());
                        preparedStatement.setInt(18, wpSsjsbFjNdDTO.getPshgj());
                        preparedStatement.setInt(19, wpSsjsbFjNdDTO.getPsjj());
                        preparedStatement.setInt(20, wpSsjsbFjNdDTO.getFjyskd());
                        preparedStatement.setInt(21, wpSsjsbFjNdDTO.getFjysgd());
                        preparedStatement.setString(22, wpSsjsbFjNdDTO.getPsry());
                        preparedStatement.setString(23, wpSsjsbFjNdDTO.getSpkzxx());
                        //证书代码
                        {
                            //wpSsjsbFjNdDTO.getZsdm()
                            preparedStatement.setString(24, "API32_YXK33YEGLXMUVMOYEQ43JWE4EU");
                        }
                        //校验码 用于校验数据的真伪
                        //A=FJHXZ,PSSJ,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSRY,ZSDM 采用英文逗号拼接后的字符串
                        String stra = concatValue(wpSsjsbFjNdDTO);
                        preparedStatement.setString(25, stra);
                        preparedStatement.setString(26, wpSsjsbFjNdDTO.getUrl());
                        preparedStatement.setString(27, wpSsjsbFjNdDTO.getPsdxzxx());

                        //int rowsAffected = preparedStatement.executeUpdate();
                        preparedStatement.addBatch();
                    }
                }
                preparedStatement.executeBatch();

            }
            //09 地块审核信息表（DKSHXX）
            createTableSQL = "CREATE TABLE DKSHXX(BSM integer,DKBSM TEXT,DKLX TEXT,XZQDM TEXT,SHJD TEXT,SHRY TEXT,SHJG TEXT,SHSM TEXT,SHSJ TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkshxxNdDTO> listdkshxxs=null;
            if(sjsbDkjbxxbByTbbsmList!=null &&sjsbDkjbxxbByTbbsmList.size()>0)
            {
                listdkshxxs = wpSjsbNdDTOMapper.getSjsbDkshxxList(sjsbDkjbxxbByTbbsmList);
            }

            if (listdkshxxs != null && listdkshxxs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO DKSHXX (BSM,DKBSM,DKLX,XZQDM,SHJD,SHRY,SHJG,SHSM,SHSJ) VALUES (?,?,?,?,?,?,?,?,?)");
                for (int i = 0; i < listdkshxxs.size(); i++) {
                    WpSjsbDkshxxNdDTO wpSjsbDkshxxNdDTO = listdkshxxs.get(i);//数据库实体

                    {
                        preparedStatement.setLong(1, wpSjsbDkshxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkshxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkshxxNdDTO.getDklx());
                        preparedStatement.setString(4, wpSjsbDkshxxNdDTO.getXzqdm());
                        preparedStatement.setString(5, wpSjsbDkshxxNdDTO.getShjd());

                        preparedStatement.setString(6, wpSjsbDkshxxNdDTO.getShry());
                        preparedStatement.setString(7, wpSjsbDkshxxNdDTO.getShjg());
                        preparedStatement.setString(8, wpSjsbDkshxxNdDTO.getShsm());
                        preparedStatement.setString(9, wpSjsbDkshxxNdDTO.getShsj());
                        preparedStatement.addBatch();
                        //int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
                preparedStatement.executeBatch();
            }
            //10 加密信息表（JMXX）
            createTableSQL = "CREATE TABLE JMXX(ZSDM TEXT,SZZS TEXT,ZSBFJGDM TEXT,ZSBFJGGY TEXT)";
            statement.executeUpdate(createTableSQL);
            UUID uuid = UUID.randomUUID();
            String hexString = uuid.toString().replace("-", "").substring(0, 32);
            {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO JMXX(ZSDM,SZZS,ZSBFJGDM,ZSBFJGGY) VALUES (?,?,?,?)");
                {
                    preparedStatement.setString(1, hexString);
                    preparedStatement.setString(2, "eyJvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwib3JnbmFtZSI6IuS4reWbveWbveWcn+WLmOa1i+inhOWIkumZoijljavniYfmiafms5XmmbrmhafmoLjmn6UpIiwiY2VydGNvZGUiOiJBUEkzMl9ZWEszM1lFR0xYTVVWTU9ZRVE0M0pXRTRFVSIsImNlcnRvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwiY2VydG9yZ25hbWUiOiLkuK3lm73lm73lnJ/li5jmtYvop4TliJLpmaIiLCJleHBpcmF0aW9uIjoiMjAyNi0xMi0zMSAxNDozNDo0NSIsInB1YmxpY2tleSI6IjA0RDMzQjMyMEVENDgxOUU0QkMwQ0JFNTc5RUE4QzFFQzVEMjNBNjM5MjJDM0E2MkM3REY0NkVFODAwNzc3MzYzQjNGOTdCM0UxMDNENDc5MzMwNTJGNzkzQkExQzU1RUFCNTgyNkI1OEE4ODZEOUZGRTI1NjVGM0M4RUZDQ0Q4QjEiLCJzaWduIjoiODU5OTU4NDEyQ0U3QzFDOTA2QjQ0NjIwREEzNjdGNzIwNjA0RDVBOEY5NkNFNkI1QUYzQzA4MTIxMjlGRTM0QjIxQjJEODExNjYzRDY0OTIzRTYwMjVCQjI5MEEzNUY3NUExQjUwQzYzQ0RGNDQyQzUxNjJFM0NDQTQ0N0Y1MkMiLCJjZXJ0dHlwZSI6IuaOpeWPo+iwg+eUqOivgeS5piIsInJlZ2lvbmNvZGUiOiIwMDAwMDAifQ==");
                    preparedStatement.setString(3, "12100000400010398P");
                    preparedStatement.setString(4, "048B570397D9FFD9EFE79382E6E75D48DA16832556214293E4BB28BA0C59C1FC397176C83612158D025BF6B51CCE554C4D795DE0F1CB1469DC83499975AB245689");
                    int rowsAffected = preparedStatement.executeUpdate();
                    //System.out.println(rowsAffected + " 行被插入");
                }
            }
            //11 版本信息表（DB_INFO)
            createTableSQL = "CREATE TABLE DB_INFO(VERSION TEXT,CREATETIME TEXT,UPDATETIME TEXT,CREATEUSER TEXT,DESCRIPTION TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建版本信息表！");

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "INSERT INTO DB_INFO (VERSION,CREATETIME,UPDATETIME,CREATEUSER,DESCRIPTION) VALUES (?,?,?,?,?)");
            {
                preparedStatement.setString(1, "2.0");
                preparedStatement.setString(2, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((new Date()).getTime()));
                preparedStatement.setNull(3, Types.VARBINARY);
                preparedStatement.setString(4, "卫片执法");
                preparedStatement.setString(5, "WPZFNDTB2024待国家核查数据同步成果包");
                int rowsAffected = preparedStatement.executeUpdate();
                //System.out.println(rowsAffected + " 行被插入");
            }
            connection.commit(); // Commit the transaction
            System.out.println("创建DB且插入数据成功！");
            rest.put("code", 200);
            rest.put("dbpath", dbPath);
            rest.put("dbpass", dbPass);
            rest.put("message", "创建DB成功");
            return rest;
        } catch (SQLException e) {
            System.out.println("连接数据库时发生错误：" + e.getMessage());
            rest.put("code", 500);
            rest.put("message", "连接数据库时发生错误：" + e.getMessage());
            return rest;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                System.out.println("关闭数据库连接时发生错误：" + e.getMessage());
            }
        }
    }


    public Map<String, Object> getDBCreate2(WpSjsbNdDTOMapper wpSjsbNdDTOMapper, String xzqdm, String status) {
        Connection connection = null;
        Statement statement = null;
        Map<String, Object> rest = new HashMap<>();
        try {
            String dbPass = UUID.randomUUID().toString().replace("-", "").substring(0, 16);
            System.out.println("pass:" + dbPass);

            // 创建保存路径
            String savePath = System.getProperty("user.dir") + "\\" + "getDBFile";
            // 保存文件的文件夹
            File folder = new File(savePath);
            // 判断路径是否存在,不存在则自动创建
            if (!folder.exists()) {
                folder.mkdirs();
            }

            String dbPath = savePath + "\\" + xzqdm + ".db";
            File file = new File(dbPath);
            if (file.exists()) {
                file.delete();
            }
            connection = SQLiteMCSqlCipherConfig.getV3Defaults().withKey(dbPass).build().createConnection("jdbc:sqlite:" + dbPath);

            statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            Map<String, Object> param = new HashMap<>();
            param.put("id", "1"); //抽取地块的查询条件
            param.put("xzqdm", xzqdm); //抽取地块的查询条件

            //根据 xzqdm 查出所有tb的bsm
            List<WpSjsbTbjbxxNdDTO> sjsbTbjbxxByStatusList = wpSjsbNdDTOMapper.getSjsbTbjbxxList(param);

            List<WpSjsbDkjbxxNdDTO> sjsbDkjbxxbByTbbsmList = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmList(sjsbTbjbxxByStatusList);

            String createTableSQL = "";

            // 01 创建扩展信息元数据表
            createTableSQL = "CREATE TABLE KZXXYSJ(BSM INT,DKLX TEXT,ZDMC TEXT,ZDBM TEXT,ZDMS TEXT,ZDLX TEXT,ZDCD INT,SFBT TEXT,SJZD TEXT,KZXXLX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbKzxxysjNdDTO> lists = wpSjsbNdDTOMapper.getallKzxxysj(param);
            if (lists != null && lists.size() > 0) {
                for (int i = 0; i < lists.size(); i++) {
                    WpSjsbKzxxysjNdDTO wpSjsbKzxxysjNdDTO = lists.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO KZXXYSJ (BSM,DKLX,ZDMC,ZDBM,ZDMS,ZDLX,ZDCD,SFBT,SJZD,KZXXLX) VALUES (?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setInt(1, wpSjsbKzxxysjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbKzxxysjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbKzxxysjNdDTO.getZdmc());
                        preparedStatement.setString(4, wpSjsbKzxxysjNdDTO.getZdbm());
                        preparedStatement.setString(5, wpSjsbKzxxysjNdDTO.getZdms());
                        preparedStatement.setString(6, wpSjsbKzxxysjNdDTO.getZdlx());
                        if (wpSjsbKzxxysjNdDTO.getZdcd() == null) {
                            preparedStatement.setNull(7, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(7, wpSjsbKzxxysjNdDTO.getZdcd());
                        }
                        preparedStatement.setString(8, wpSjsbKzxxysjNdDTO.getSfbt());
                        preparedStatement.setString(9, wpSjsbKzxxysjNdDTO.getSjzd());
                        preparedStatement.setString(10, wpSjsbKzxxysjNdDTO.getKzxxlx());
                        int rowsAffected = preparedStatement.executeUpdate();
                    }
                }
            }

            // 02 图斑基本信息 TBJBXX
            createTableSQL = "CREATE TABLE TBJBXX(BSM TEXT,DKLX TEXT,XZQDM TEXT,XMC TEXT,TBBH TEXT,SFBHZDK TEXT,TBMJ REAL,XZB REAL,YZB REAL,BZ TEXT,KZXX TEXT,DKFW TEXT, SJDRLY TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbjbxxNdDTO> listtbjbxxs = wpSjsbNdDTOMapper.getSjsbTbjbxxList(param);
            if (listtbjbxxs != null && listtbjbxxs.size() > 0) {
                for (int i = 0; i < listtbjbxxs.size(); i++) {
                    WpSjsbTbjbxxNdDTO wpSjsbTbjbxxNdDTO = listtbjbxxs.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO TBJBXX (BSM,DKLX,XZQDM,XMC,TBBH,SFBHZDK,TBMJ,XZB,YZB,BZ,KZXX,DKFW,SJDRLY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbTbjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbjbxxNdDTO.getDklx());
                        preparedStatement.setString(3, wpSjsbTbjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbTbjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbTbjbxxNdDTO.getTbbh());
                        preparedStatement.setString(6, wpSjsbTbjbxxNdDTO.getSfbhzdk());
                        preparedStatement.setDouble(7, wpSjsbTbjbxxNdDTO.getTbmj());
                        preparedStatement.setDouble(8, wpSjsbTbjbxxNdDTO.getXzb());
                        preparedStatement.setDouble(9, wpSjsbTbjbxxNdDTO.getYzb());
                        preparedStatement.setString(10, wpSjsbTbjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbTbjbxxNdDTO.getKzxx());
                        preparedStatement.setString(12, wpSjsbTbjbxxNdDTO.getDkfw());
                        preparedStatement.setString(13, wpSjsbTbjbxxNdDTO.getSjdrly());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //03 图斑调查核实信息表（TBDCHSXX）
            createTableSQL = "CREATE TABLE TBDCHSXX(BSM TEXT,TBBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT, KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbTbdchsxxNdDTO> listtbdchsxxs = wpSjsbNdDTOMapper.getallTbdchsxx(sjsbDkjbxxbByTbbsmList);
            if (listtbdchsxxs != null && listtbdchsxxs.size() > 0) {
                for (int i = 0; i < listtbdchsxxs.size(); i++) {
                    WpSjsbTbdchsxxNdDTO wpSjsbTbdchsxxNdDTO = listtbdchsxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO TBDCHSXX (BSM,TBBSM,WYHSQK,DCRY,DCSJ, KZXX) VALUES (?,?,?,?,?,?)");
                    {
                        preparedStatement.setLong(1, wpSjsbTbdchsxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbTbdchsxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbTbdchsxxNdDTO.getWyhsqk());
                        preparedStatement.setString(4, wpSjsbTbdchsxxNdDTO.getDcry());
                        preparedStatement.setString(5, wpSjsbTbdchsxxNdDTO.getDcsj());
                        preparedStatement.setString(6, wpSjsbTbdchsxxNdDTO.getKzxx());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //04 地块基本信息表（DKJBXX）
            createTableSQL = "CREATE TABLE DKJBXX( BSM  TEXT,TBBSM  TEXT,XZQDM  TEXT,XMC  TEXT,DKBH  TEXT,DKMC  TEXT,DKMJ  REAL,XZB  REAL,YZB  REAL,BZ  TEXT,DKFW  TEXT,KZXX  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkjbxxNdDTO> listdkjbxxbs = wpSjsbNdDTOMapper.getSjsbDkjbxxbByTbbsmList(sjsbTbjbxxByStatusList);
            Gson gson1 = new GsonBuilder().serializeNulls().create();
            if (listdkjbxxbs != null && listdkjbxxbs.size() > 0) {
                for (int i = 0; i < listdkjbxxbs.size(); i++) {
                    WpSjsbDkjbxxNdDTO wpSjsbDkjbxxNdDTO = listdkjbxxbs.get(i);//数据库实体
                    Map<String, Object> dkmap = new HashMap<>();
                    dkmap.put("SFBGJSYD", wpSjsbDkjbxxNdDTO.getSfbgjsyd());
                    dkmap.put("SFGDWF", wpSjsbDkjbxxNdDTO.getSfgdwf());
                    dkmap.put("GDMJ", wpSjsbDkjbxxNdDTO.getGdmj());
                    dkmap.put("QTNYDMJ", wpSjsbDkjbxxNdDTO.getQtnydmj());
                    dkmap.put("JSYDMJ", wpSjsbDkjbxxNdDTO.getJsydmj());
                    dkmap.put("WLYDMJ", wpSjsbDkjbxxNdDTO.getWlydmj());
                    dkmap.put("PDLX", wpSjsbDkjbxxNdDTO.getPdlx());
                    dkmap.put("HF_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx1()));
                    dkmap.put("HF_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getHf_jtqx2()));
                    dkmap.put("HF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getHf_sjyt()));
                    dkmap.put("HF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getHf_pdyjsm()));
                    dkmap.put("WF_WFLX1", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx1()));
                    dkmap.put("WF_WFLX2", isNull(wpSjsbDkjbxxNdDTO.getWf_wflx2()));
                    dkmap.put("WF_SJYT", isNull(wpSjsbDkjbxxNdDTO.getWf_sjyt()));
                    dkmap.put("SFLZGDJF", isNull(wpSjsbDkjbxxNdDTO.getSflzgdjf()));
                    dkmap.put("SFWHZJ", isNull(wpSjsbDkjbxxNdDTO.getSfwhzj()));
                    dkmap.put("SFDPF", isNull(wpSjsbDkjbxxNdDTO.getSfdpf()));
                    dkmap.put("WF_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getWf_pdyjsm()));
                    dkmap.put("XMMC", isNull(wpSjsbDkjbxxNdDTO.getXmmc()));
                    dkmap.put("XMZT", isNull(wpSjsbDkjbxxNdDTO.getXmzt()));
                    dkmap.put("XMLX", isNull(wpSjsbDkjbxxNdDTO.getXmlx()));
                    dkmap.put("LXPZJG", isNull(wpSjsbDkjbxxNdDTO.getLxpzjg()));
                    dkmap.put("LXPZWH", isNull(wpSjsbDkjbxxNdDTO.getLxpzwh()));
                    dkmap.put("WF_SFLA", isNull(wpSjsbDkjbxxNdDTO.getWf_sfla()));
                    dkmap.put("QT_JTQX1", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx1()));
                    dkmap.put("QT_JTQX2", isNull(wpSjsbDkjbxxNdDTO.getQt_jtqx2()));
                    dkmap.put("PWSJ", isNull(wpSjsbDkjbxxNdDTO.getPwsj()));
                    dkmap.put("QT_PDYJSM", isNull(wpSjsbDkjbxxNdDTO.getQt_pdyjsm()));
                    dkmap.put("SFNRHSQD", isNull(wpSjsbDkjbxxNdDTO.getSfnrhsqd()));
                    dkmap.put("XZJD", isNull(wpSjsbDkjbxxNdDTO.getXzjd()));
                    dkmap.put("CSMC", isNull(wpSjsbDkjbxxNdDTO.getCsmc()));
                    dkmap.put("XXDZ", isNull(wpSjsbDkjbxxNdDTO.getXxdz()));
                    String listToJsonString1 = gson1.toJson(dkmap);

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKJBXX (BSM,TBBSM,XZQDM,XMC,DKBH,DKMC,DKMJ,XZB,YZB,BZ,DKFW,KZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbDkjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkjbxxNdDTO.getTbbsm());
                        preparedStatement.setString(3, wpSjsbDkjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkjbxxNdDTO.getXmc());
                        preparedStatement.setString(5, wpSjsbDkjbxxNdDTO.getDkbh());
                        preparedStatement.setString(6, wpSjsbDkjbxxNdDTO.getDkmc());
                        if (wpSjsbDkjbxxNdDTO.getDkmj() == null) {
                            preparedStatement.setNull(7, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(7, wpSjsbDkjbxxNdDTO.getDkmj());
                        }
                        if (wpSjsbDkjbxxNdDTO.getXzb() == null) {
                            preparedStatement.setNull(8, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(8, wpSjsbDkjbxxNdDTO.getXzb());
                        }
                        if (wpSjsbDkjbxxNdDTO.getYzb() == null) {
                            preparedStatement.setNull(9, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(9, wpSjsbDkjbxxNdDTO.getYzb());
                        }
                        preparedStatement.setString(10, wpSjsbDkjbxxNdDTO.getBz());
                        preparedStatement.setString(11, wpSjsbDkjbxxNdDTO.getDkfw());
                        preparedStatement.setString(12, listToJsonString1);
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //05 地块调查核实信息表（DKDCHSXX)
            createTableSQL = "CREATE TABLE DKDCHSXX(DKBSM TEXT,WYHSQK TEXT,DCRY TEXT,DCSJ TEXT,KZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkdchssxNdDTO> listdkdchsxxs = wpSjsbNdDTOMapper.getallDkdchsxx(sjsbDkjbxxbByTbbsmList);
            if (listdkdchsxxs != null && listdkdchsxxs.size() > 0) {
                for (int i = 0; i < listdkdchsxxs.size(); i++) {
                    WpSjsbDkdchssxNdDTO wpSjsbDkdchssxNdDTO = listdkdchsxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKDCHSXX (DKBSM,WYHSQK,DCRY,DCSJ,KZXX) VALUES (?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbDkdchssxNdDTO.getDkbsm());
                        preparedStatement.setString(2, wpSjsbDkdchssxNdDTO.getWyhsqk());
                        preparedStatement.setString(3, wpSjsbDkdchssxNdDTO.getDcry());
                        preparedStatement.setString(4, wpSjsbDkdchssxNdDTO.getDcsj());
                        preparedStatement.setString(5, wpSjsbDkdchssxNdDTO.getKzxx());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //06 地块附件信息表（DKFJJBXX）
            createTableSQL = "CREATE TABLE DKFJJBXX(BSM INTEGER,DKBSM TEXT,XZQDM TEXT,FJMC TEXT,FJLX   TEXT,SCRY  TEXT,SCSJ  TEXT,FJDX  INTEGER,MD5  TEXT,FJ BLOB,URL TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建地块附件信息表！");
            List<WpSjsbDkfjjbxxNdDTO> listdkfjjbxxs = wpSjsbNdDTOMapper.getSjsbDkfjjbxxList(sjsbDkjbxxbByTbbsmList);
            if (listdkdchsxxs != null && listdkfjjbxxs.size() > 0) {
                for (int i = 0; i < listdkfjjbxxs.size(); i++) {
                    WpSjsbDkfjjbxxNdDTO wpSjsbDkfjjbxxNdDTO = listdkfjjbxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKFJJBXX (BSM,DKBSM,XZQDM,FJMC,FJLX,SCRY,SCSJ,FJDX,MD5,FJ,URL) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setLong(1, wpSjsbDkfjjbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkfjjbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkfjjbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkfjjbxxNdDTO.getFjmc());
                        preparedStatement.setString(5, wpSjsbDkfjjbxxNdDTO.getFjlx());
                        preparedStatement.setString(6, wpSjsbDkfjjbxxNdDTO.getScry());
                        preparedStatement.setString(7, wpSjsbDkfjjbxxNdDTO.getScsj());
                        if (wpSjsbDkfjjbxxNdDTO.getFjdx() == null) {
                            preparedStatement.setNull(8, java.sql.Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkfjjbxxNdDTO.getFjdx());
                        }
                        preparedStatement.setString(9, wpSjsbDkfjjbxxNdDTO.getMd5());
                        if (wpSjsbDkfjjbxxNdDTO.getFj() == null) {
                            preparedStatement.setNull(10, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(10, wpSjsbDkfjjbxxNdDTO.getFj());
                        }
                        preparedStatement.setString(11, wpSjsbDkfjjbxxNdDTO.getUrl());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //07 地块批文信息表（DKPWJBXX）
            createTableSQL = "CREATE TABLE DKPWJBXX(BSM  TEXT, DKBSM  TEXT,XZQDM  TEXT,PZWH  TEXT,PZWHLX  TEXT,PZWHLXTWO  TEXT,PZJG  TEXT,PWLY INTEGER,PZSJ  TEXT,PZMJ  REAL,PZGDMJ  REAL,PZNYDMJ  REAL,PZYJJBNTMJ  REAL,YXQZ  TEXT,PWLB  TEXT,XMMC  TEXT,PZJSYDMJ  REAL,PZWLYMJ  REAL,FJURL  TEXT,SHPURL  TEXT,MD5  TEXT,REASON  TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkpwjbbxxNdDTO> listdkpwjbxxs = wpSjsbNdDTOMapper.getSjsbDkwpjbxxList(sjsbDkjbxxbByTbbsmList);
            if (listdkpwjbxxs != null && listdkpwjbxxs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listdkpwjbxxs.size(); i++) {
                    WpSjsbDkpwjbbxxNdDTO wpSjsbDkpwjbbxxNdDTO = listdkpwjbxxs.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKPWJBXX (BSM,DKBSM,XZQDM,PZWH,PZWHLX,PZWHLXTWO,PZJG,PWLY,PZSJ,PZMJ,PZGDMJ,PZNYDMJ,PZYJJBNTMJ,YXQZ,PWLB,XMMC,PZJSYDMJ,PZWLYMJ,FJURL,SHPURL,MD5,REASON) " +
                                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSjsbDkpwjbbxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkpwjbbxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkpwjbbxxNdDTO.getXzqdm());
                        preparedStatement.setString(4, wpSjsbDkpwjbbxxNdDTO.getPzwh());
                        preparedStatement.setString(5, wpSjsbDkpwjbbxxNdDTO.getPzwhlx());
                        preparedStatement.setString(6, wpSjsbDkpwjbbxxNdDTO.getPzwhlxtwo());
                        preparedStatement.setString(7, wpSjsbDkpwjbbxxNdDTO.getPzjg());
                        if (wpSjsbDkpwjbbxxNdDTO.getPwly() == null) {
                            preparedStatement.setInt(8, Types.INTEGER);
                        } else {
                            preparedStatement.setInt(8, wpSjsbDkpwjbbxxNdDTO.getPwly());
                        }
                        preparedStatement.setString(9, wpSjsbDkpwjbbxxNdDTO.getPzsj());
                        preparedStatement.setDouble(10, wpSjsbDkpwjbbxxNdDTO.getPzmj());
                        preparedStatement.setDouble(11, wpSjsbDkpwjbbxxNdDTO.getPzgdmj());
                        preparedStatement.setDouble(12, wpSjsbDkpwjbbxxNdDTO.getPznydmj());

                        preparedStatement.setDouble(13, wpSjsbDkpwjbbxxNdDTO.getPzyjjbntmj());
                        preparedStatement.setString(14, wpSjsbDkpwjbbxxNdDTO.getYxqz());
                        preparedStatement.setString(15, wpSjsbDkpwjbbxxNdDTO.getPwlb());
                        preparedStatement.setString(16, wpSjsbDkpwjbbxxNdDTO.getXmmc());
                        if (wpSjsbDkpwjbbxxNdDTO.getPzjsydmj() == null) {
                            preparedStatement.setNull(17, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(17, wpSjsbDkpwjbbxxNdDTO.getPzjsydmj());
                        }
                        if (wpSjsbDkpwjbbxxNdDTO.getPzwlymj() == null) {
                            preparedStatement.setNull(18, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(18, wpSjsbDkpwjbbxxNdDTO.getPzwlymj());
                        }
                        preparedStatement.setString(19, wpSjsbDkpwjbbxxNdDTO.getFjurl());
                        preparedStatement.setString(20, wpSjsbDkpwjbbxxNdDTO.getShpurl());
                        preparedStatement.setString(21, wpSjsbDkpwjbbxxNdDTO.getMd5());
                        preparedStatement.setString(22, wpSjsbDkpwjbbxxNdDTO.getReason());

                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //08 附件表（FJ）
            createTableSQL = "CREATE TABLE FJ(BSM TEXT,DKLX TEXT,DKBSM TEXT,ZDKBH TEXT,XZQDM TEXT,FJMC TEXT,FJLX TEXT,PSTZ TEXT,FJ,FJHXZ TEXT,PSSJ TEXT,XDGD REAL,JDGD REAL,Longitude REAL,Latitude REAL,PSFYJ INT,PSJD INT,PSHGJ INT,PSJJ INT,FJYSKD INT,FJYSGD INT,PSRY TEXT,SPKZXX TEXT,ZSDM TEXT,JYM TEXT,URL TEXT,PSDXZXX TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbFjNdDTO> listfjs = wpSjsbNdDTOMapper.getSjsbFjList(sjsbDkjbxxbByTbbsmList);
            if (listfjs != null && listfjs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listfjs.size(); i++) {
                    WpSjsbFjNdDTO wpSsjsbFjNdDTO = listfjs.get(i);//数据库实体
                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO FJ (BSM,DKLX,DKBSM,ZDKBH,XZQDM,FJMC,FJLX,PSTZ,FJ,FJHXZ,PSSJ,XDGD,JDGD,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSJJ,FJYSKD,FJYSGD,PSRY,SPKZXX,ZSDM,JYM,URL,PSDXZXX) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setString(1, wpSsjsbFjNdDTO.getBsm());
                        preparedStatement.setString(2, wpSsjsbFjNdDTO.getDklx());
                        preparedStatement.setString(3, wpSsjsbFjNdDTO.getDkbsm());
                        preparedStatement.setString(4, wpSsjsbFjNdDTO.getZdkbh());
                        preparedStatement.setString(5, wpSsjsbFjNdDTO.getXzqdm());
                        preparedStatement.setString(6, wpSsjsbFjNdDTO.getFjmc());
                        preparedStatement.setString(7, wpSsjsbFjNdDTO.getFjlx());
                        preparedStatement.setString(8, wpSsjsbFjNdDTO.getPstz());
                        if (wpSsjsbFjNdDTO.getFj() == null) {
                            preparedStatement.setNull(9, java.sql.Types.BLOB);
                        } else {
                            preparedStatement.setBlob(9, wpSsjsbFjNdDTO.getFj());
                        }
                        preparedStatement.setString(10, wpSsjsbFjNdDTO.getFjhxz());
                        preparedStatement.setString(11, wpSsjsbFjNdDTO.getPssj());


                        if (wpSsjsbFjNdDTO.getXdgd() == null) {
                            preparedStatement.setNull(12, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(12, wpSsjsbFjNdDTO.getXdgd());
                        }

                        if (wpSsjsbFjNdDTO.getJdgd() == null) {
                            preparedStatement.setNull(13, Types.FLOAT);
                        } else {
                            preparedStatement.setDouble(13, wpSsjsbFjNdDTO.getJdgd());
                        }

                        preparedStatement.setDouble(14, wpSsjsbFjNdDTO.getLongitude());
                        preparedStatement.setDouble(15, wpSsjsbFjNdDTO.getLatitude());
                        preparedStatement.setInt(16, wpSsjsbFjNdDTO.getPsfyj());
                        preparedStatement.setInt(17, wpSsjsbFjNdDTO.getPsjd());
                        preparedStatement.setInt(18, wpSsjsbFjNdDTO.getPshgj());
                        preparedStatement.setInt(19, wpSsjsbFjNdDTO.getPsjj());
                        preparedStatement.setInt(20, wpSsjsbFjNdDTO.getFjyskd());
                        preparedStatement.setInt(21, wpSsjsbFjNdDTO.getFjysgd());
                        preparedStatement.setString(22, wpSsjsbFjNdDTO.getPsry());
                        preparedStatement.setString(23, wpSsjsbFjNdDTO.getSpkzxx());
                        //证书代码
                        {
                            //wpSsjsbFjNdDTO.getZsdm()
                            preparedStatement.setString(24, "API32_YXK33YEGLXMUVMOYEQ43JWE4EU");
                        }
                        //校验码 用于校验数据的真伪
                        //A=FJHXZ,PSSJ,Longitude,Latitude,PSFYJ,PSJD,PSHGJ,PSRY,ZSDM 采用英文逗号拼接后的字符串
                        String stra = concatValue(wpSsjsbFjNdDTO);
                        preparedStatement.setString(25, stra);
                        preparedStatement.setString(26, wpSsjsbFjNdDTO.getUrl());
                        preparedStatement.setString(27, wpSsjsbFjNdDTO.getPsdxzxx());

                        int rowsAffected = preparedStatement.executeUpdate();
                    }
                }
            }
            //09 地块审核信息表（DKSHXX）
            createTableSQL = "CREATE TABLE DKSHXX(BSM integer,DKBSM TEXT,DKLX TEXT,XZQDM TEXT,SHJD TEXT,SHRY TEXT,SHJG TEXT,SHSM TEXT,SHSJ TEXT)";
            statement.executeUpdate(createTableSQL);
            List<WpSjsbDkshxxNdDTO> listdkshxxs = wpSjsbNdDTOMapper.getSjsbDkshxxList(sjsbDkjbxxbByTbbsmList);
            if (listdkshxxs != null && listdkshxxs.size() > 0) {
                //FJURL,SHPURL,MD5,REASON
                for (int i = 0; i < listdkshxxs.size(); i++) {
                    WpSjsbDkshxxNdDTO wpSjsbDkshxxNdDTO = listdkshxxs.get(i);//数据库实体

                    PreparedStatement preparedStatement = connection.prepareStatement(
                            "INSERT INTO DKSHXX (BSM,DKBSM,DKLX,XZQDM,SHJD,SHRY,SHJG,SHSM,SHSJ) VALUES (?,?,?,?,?,?,?,?,?)");
                    {
                        preparedStatement.setLong(1, wpSjsbDkshxxNdDTO.getBsm());
                        preparedStatement.setString(2, wpSjsbDkshxxNdDTO.getDkbsm());
                        preparedStatement.setString(3, wpSjsbDkshxxNdDTO.getDklx());
                        preparedStatement.setString(4, wpSjsbDkshxxNdDTO.getXzqdm());
                        preparedStatement.setString(5, wpSjsbDkshxxNdDTO.getShjd());

                        preparedStatement.setString(6, wpSjsbDkshxxNdDTO.getShry());
                        preparedStatement.setString(7, wpSjsbDkshxxNdDTO.getShjg());
                        preparedStatement.setString(8, wpSjsbDkshxxNdDTO.getShsm());
                        preparedStatement.setString(9, wpSjsbDkshxxNdDTO.getShsj());
                        int rowsAffected = preparedStatement.executeUpdate();
                        //System.out.println(rowsAffected + " 行被插入");
                    }
                }
            }
            //10 加密信息表（JMXX）
            createTableSQL = "CREATE TABLE JMXX(ZSDM TEXT,SZZS TEXT,ZSBFJGDM TEXT,ZSBFJGGY TEXT)";
            statement.executeUpdate(createTableSQL);
            UUID uuid = UUID.randomUUID();
            String hexString = uuid.toString().replace("-", "").substring(0, 32);
            {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO JMXX(ZSDM,SZZS,ZSBFJGDM,ZSBFJGGY) VALUES (?,?,?,?)");
                {
                    preparedStatement.setString(1, hexString);
                    preparedStatement.setString(2, "eyJvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwib3JnbmFtZSI6IuS4reWbveWbveWcn+WLmOa1i+inhOWIkumZoijljavniYfmiafms5XmmbrmhafmoLjmn6UpIiwiY2VydGNvZGUiOiJBUEkzMl9ZWEszM1lFR0xYTVVWTU9ZRVE0M0pXRTRFVSIsImNlcnRvcmdjb2RlIjoiMTIxMDAwMDA0MDAwMTAzOThQIiwiY2VydG9yZ25hbWUiOiLkuK3lm73lm73lnJ/li5jmtYvop4TliJLpmaIiLCJleHBpcmF0aW9uIjoiMjAyNi0xMi0zMSAxNDozNDo0NSIsInB1YmxpY2tleSI6IjA0RDMzQjMyMEVENDgxOUU0QkMwQ0JFNTc5RUE4QzFFQzVEMjNBNjM5MjJDM0E2MkM3REY0NkVFODAwNzc3MzYzQjNGOTdCM0UxMDNENDc5MzMwNTJGNzkzQkExQzU1RUFCNTgyNkI1OEE4ODZEOUZGRTI1NjVGM0M4RUZDQ0Q4QjEiLCJzaWduIjoiODU5OTU4NDEyQ0U3QzFDOTA2QjQ0NjIwREEzNjdGNzIwNjA0RDVBOEY5NkNFNkI1QUYzQzA4MTIxMjlGRTM0QjIxQjJEODExNjYzRDY0OTIzRTYwMjVCQjI5MEEzNUY3NUExQjUwQzYzQ0RGNDQyQzUxNjJFM0NDQTQ0N0Y1MkMiLCJjZXJ0dHlwZSI6IuaOpeWPo+iwg+eUqOivgeS5piIsInJlZ2lvbmNvZGUiOiIwMDAwMDAifQ==");
                    preparedStatement.setString(3, "12100000400010398P");
                    preparedStatement.setString(4, "048B570397D9FFD9EFE79382E6E75D48DA16832556214293E4BB28BA0C59C1FC397176C83612158D025BF6B51CCE554C4D795DE0F1CB1469DC83499975AB245689");
                    int rowsAffected = preparedStatement.executeUpdate();
                    //System.out.println(rowsAffected + " 行被插入");
                }
            }
            //11 版本信息表（DB_INFO)
            createTableSQL = "CREATE TABLE DB_INFO(VERSION TEXT,CREATETIME TEXT,UPDATETIME TEXT,CREATEUSER TEXT,DESCRIPTION TEXT)";
            statement.executeUpdate(createTableSQL);
            System.out.println("成功创建版本信息表！");

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "INSERT INTO DB_INFO (VERSION,CREATETIME,UPDATETIME,CREATEUSER,DESCRIPTION) VALUES (?,?,?,?,?)");
            {
                preparedStatement.setString(1, "2.0");
                preparedStatement.setString(2, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((new Date()).getTime()));
                preparedStatement.setNull(3, Types.VARBINARY);
                preparedStatement.setString(4, "卫片执法");
                preparedStatement.setString(5, "WPZFNDTB2024待国家核查数据同步成果包");
                int rowsAffected = preparedStatement.executeUpdate();
                //System.out.println(rowsAffected + " 行被插入");
            }

            System.out.println("创建DB且插入数据成功！");
            rest.put("code", 200);
            rest.put("dbpath", dbPath);
            rest.put("dbpass", dbPass);
            rest.put("message", "创建DB成功");
            return rest;
        } catch (SQLException e) {
            System.out.println("连接数据库时发生错误：" + e.getMessage());
            rest.put("code", 500);
            rest.put("message", "连接数据库时发生错误：" + e.getMessage());
            return rest;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                System.out.println("关闭数据库连接时发生错误：" + e.getMessage());
            }
        }
    }


}